Home » RDBMS Server » Performance Tuning » Sql behaving badly after upgrade (merged) (19c)
Sql behaving badly after upgrade (merged) [message #686589] Tue, 18 October 2022 10:48 Go to next message
nishant1987
Messages: 8
Registered: September 2022
Junior Member
One of the sql is causing performance issue and taking long time to complete after 19c upgrade


SELECT DISTINCT
CT.TRX_NUMBER
INVOICE_NUMBER,
CT.DOC_SEQUENCE_VALUE
FISCAL_NUMBER,
CL.LINE_NUMBER
LINE_NUM,
HPA.PARTY_NAME
COMPANY_NAME,
HAT.NAME
BUSINESS_UNIT_NAME,
LPH.SIC_CODE
SIC_CODE,
REPLACE (LPH.SIC_DESCRIPTION, ';', ',')
SIC_DESCRIPTION,
TO_CHAR (OOHA.ORDER_NUMBER)
SO_NUMBER,
JRS.NAME
SALESPERSON,
TO_CHAR (OOLA.LINE_NUMBER)
SO_LINE_NUMBER,
PP.SEGMENT1
PROJECT_NUMBER,
PT.TASK_NUMBER
TASK_NUMBER,
REPLACE (
REPLACE (
REPLACE (REPLACE (CL.DESCRIPTION, ';', ','), CHR (10), NULL),
CHR (09),
NULL),
CHR (13),
NULL)
ITEM_DESCRIPTION,
CL.QUANTITY_INVOICED
QTY,
CT.TRX_DATE
INVOICE_DATE,
RC.NAME
TRANSACTION_TYPE,
CT.INVOICE_CURRENCY_CODE
FUNCTIONAL_CURRENCY_FC,
NVL (CSTI.ITEM_COST, 0)
UNIT_COST_FC,
(NVL (CSTI.ITEM_COST, 0) * NVL (CL.QUANTITY_INVOICED, 0))
TOTAL_COST_FC,
DECODE (CT.INVOICE_CURRENCY_CODE,
'BRL', CL.EXTENDED_AMOUNT,
CL.EXTENDED_AMOUNT * CT.EXCHANGE_RATE)
INVOICED_AMOUNT_FC,
HLA.LOCATION_CODE
SHIPPING_ORGANIZATION,
MS.SEGMENT1
ORDERED_ITEM,
GCV_REV.CONCATENATED_SEGMENTS
SALES_ACCOUNT,
GCV_COST.CONCATENATED_SEGMENTS
COST_OF_GOODS_SOLD_ACCOUNT,
CL.GLOBAL_ATTRIBUTE2
FISCAL_CLASSIFICATION_CODE,
PP.SEGMENT1
MRO_PROJECT,
HLO.CITY
CUSTOMER_CITY,
CL.GLOBAL_ATTRIBUTE4
ITEM_ORIGIN,
REPLACE (
REPLACE (
REPLACE (REPLACE (MT.DESCRIPTION, ';', ','), CHR (10), NULL),
CHR (09),
NULL),
CHR (13),
NULL)
ITEM_PORTUGUESE_DESCRIPTION,
LPH.PORECEIPTDATE
PO_RECEIVED_DATE,
CL.GLOBAL_ATTRIBUTE3
TRANSACTION_CONDITION_CLASS,
HLO.STATE
CUSTOMER_STATE,
RC.GLOBAL_ATTRIBUTE3 || '|' || RC.DESCRIPTION
TIPO_DE_FATURAMENTO,
CSTI.COST_TYPE
COST_TYPE,
HCA.ACCOUNT_NUMBER
CUSTOMER_CODE,
CT.CUSTOMER_TRX_ID,
DECODE (HCA.CUSTOMER_TYPE, 'R', 'Externo', 'I', 'Interno')
TYPE_CUSTOMER,
CT.STATUS_TRX
STATUS_TRX,
OTTT.NAME
SO_ORDER_TYPE,
RC.TYPE
RC_TYPE,
REPLACE (
REPLACE (
REPLACE (REPLACE (CT.ATTRIBUTE1, ';', ':'), CHR (10), NULL),
CHR (13),
NULL),
CHR (09),
NULL)
FINAL_DELIVERY_DATE,
LPL.REQUESTED_DELIVERY_DATE
REQ_DELIVERY_DATE,
OOLA.REQUEST_DATE
REQ_SHIP_DATE,
LPL.ORIGINAL_PROMISE_DATE
ORIGINAL_PROMISED_DATE,
OOLA.PROMISE_DATE
PROMISE_DATE,
OOLA.SCHEDULE_SHIP_DATE
SCHEDULE_SHIP_DATE,
REPLACE (OOHA.CUST_PO_NUMBER, CHR (13), NULL)
CUSTOMER_PO,
OTT.NAME
LINE_TYPE,
LPH.PROJECT_TYPE
TIER_TYPE,
TT.TASK_TYPE
TASK_TYPE,
LPH.KIND_OF_BUSINESS
KOB_HEADER,
LPL.KOB3
KOB_LINE,
FLV_L.MEANING
SHIPPING_TERMS,
OOLA.SHIPMENT_PRIORITY_CODE
SHIPMENT_PRIORITY,
OOS.NAME
ORDER_SOURCE,
DECODE (OOHA.SOURCE_DOCUMENT_TYPE_ID,
16, (SELECT TO_CHAR (AQH.QUOTE_NUMBER) QUOTE_NUMBER
FROM APPS.ASO_QUOTE_HEADERS AQH
WHERE AQH.QUOTE_HEADER_ID = OOHA.SOURCE_DOCUMENT_ID),
NULL)
QUOTE_NUMBER,
PP.PROJECT_TYPE
PROJECT_TYPE,
PP.NAME
PROJECT_NAME,
(SELECT LPAD (PERIOD_NUM, 2, '0')
FROM APPS.GL_PERIOD_STATUSES
WHERE SET_OF_BOOKS_ID IN (SELECT ATTRIBUTE1
FROM APPS.GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = 443)
AND APPLICATION_ID = 101
AND TRUNC (CT.TRX_DATE) BETWEEN TRUNC (START_DATE)
AND TRUNC (END_DATE))
PERIOD,
OOLA.SHIP_FROM_ORG_ID
SHIP_FROM_ORG_ID,
OOLA.LINE_ID
OE_LINE_ID,
OOLA.HEADER_ID
OE_HEADER_ID,
OOLA.INVENTORY_ITEM_ID
INVENTORY_ITEM_ID,
CL.CUSTOMER_TRX_LINE_ID
CUSTOMER_TRX_LINE_ID,
CT.ORG_ID
ORG_ID,
CT.EXCHANGE_RATE
EXCHANGE_RATE,
EXTENDED_AMOUNT
EXTENDED_AMOUNT,
GCV_REV.CODE_COMBINATION_ID
REV_CODE_COMBINATION_ID,
CL.WAREHOUSE_ID
WAREHOUSE_ID,
OOHA.END_CUSTOMER_SITE_USE_ID
END_CUSTOMER_SITE_USE_ID,
(SELECT LPH1.SERVICE_CLOUD_REF
FROM APPS.XXOM_3LP_SYM_ORA_ORDER_HDR LPH1
WHERE LPH1.HEADER_ID = OOHA.HEADER_ID)
SERVICE_CLOUD_REF
FROM APPS.RA_CUSTOMER_TRX CT,
APPS.RA_CUSTOMER_TRX_LINES CL,
APPS.RA_CUST_TRX_TYPES RC,
APPS.RA_BATCH_SOURCES BS,
APPS.MTL_SYSTEM_ITEMS_B MS,
APPS.MTL_SYSTEM_ITEMS_TL MT,
APPS.CST_ITEM_COST_TYPE_V CSTI,
APPS.HR_LOCATIONS HLA,
APPS.HZ_PARTIES HPA,
APPS.HZ_CUST_ACCOUNTS HCA,
APPS.HZ_PARTY_SITES HPS,
APPS.HZ_LOCATIONS HLO,
APPS.HZ_CUST_ACCT_SITES HCS,
APPS.HZ_CUST_SITE_USES HCU,
APPS.HR_ALL_ORGANIZATION_UNITS HAO,
APPS.HR_ALL_ORGANIZATION_UNITS_TL HAT,
APPS.GL_CODE_COMBINATIONS_KFV GCV_REV,
APPS.RA_CUST_TRX_LINE_GL_DIST RCG,
APPS.GL_CODE_COMBINATIONS_KFV GCV_COST,
APPS.OE_ORDER_LINES OOLA,
APPS.OE_ORDER_HEADERS OOHA,
APPS.OE_TRANSACTION_TYPES_TL OTTT,
APPS.PA_PROJECTS PP,
APPS.PA_TASKS PT,
APPS.XXOM_3LP_SYM_ORA_ORDER_LINES LPL,
APPS.XXOM_3LP_SYM_ORA_ORDER_HDR LPH,
APPS.OE_ORDER_SOURCES OOS,
APPS.OE_TRANSACTION_TYPES OTT,
APPS.FND_LOOKUP_VALUES FLV_L,
APPS.JTF_RS_SALESREPS JRS,
APPS.AR_NOTES AN,
(SELECT PPE.PROJ_ELEMENT_ID, PTT.TASK_TYPE
FROM APPS.PA_PROJ_ELEMENTS PPE, APPS.PA_TASK_TYPES PTT
WHERE PPE.TYPE_ID = PTT.TASK_TYPE_ID) TT
WHERE HPA.PARTY_ID(+) = HCA.PARTY_ID
AND HCA.PARTY_ID(+) = HPS.PARTY_ID
AND HPS.LOCATION_ID = HLO.LOCATION_ID(+)
AND HPS.PARTY_SITE_ID(+) = HCS.PARTY_SITE_ID
AND HCS.CUST_ACCT_SITE_ID(+) = HCU.CUST_ACCT_SITE_ID
AND ( ( OOS.NAME != 'Internal'
AND HCU.SITE_USE_ID = CT.SHIP_TO_SITE_USE_ID
AND HCU.SITE_USE_CODE = 'SHIP_TO')
OR ( OOS.NAME = 'Internal'
AND HCU.SITE_USE_ID = CT.BILL_TO_SITE_USE_ID
AND HCU.SITE_USE_CODE = 'BILL_TO'))
AND CT.CUSTOMER_TRX_ID = CL.CUSTOMER_TRX_ID
AND CT.COMPLETE_FLAG = 'Y'
AND CL.LINE_TYPE = 'LINE'
AND CT.CUST_TRX_TYPE_ID = RC.CUST_TRX_TYPE_ID
AND RC.ORG_ID = CT.ORG_ID
AND RC.TYPE = 'INV'
AND ( RC.ACCOUNTING_AFFECT_FLAG = 'Y'
OR (SELECT DECODE (FLV.TAG, 'INCLUDE', 'Y', 'N')
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'EMR AR SALES X FUTURE DLVY BR'
AND FLV.LANGUAGE = USERENV ('LANG')
AND FLV.ENABLED_FLAG = 'Y'
AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
TRUNC (SYSDATE)
AND NVL (FLV.END_DATE_ACTIVE, TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE)
AND FLV.MEANING = RC.NAME) =
'Y')
AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND BS.ORG_ID = CT.ORG_ID
AND BS.BATCH_SOURCE_TYPE = 'FOREIGN'
AND CL.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID(+)
AND CL.WAREHOUSE_ID = MS.ORGANIZATION_ID(+)
AND CL.WAREHOUSE_ID = HLA.INVENTORY_ORGANIZATION_ID(+)
AND HLA.INVENTORY_ORGANIZATION_ID = HAO.ORGANIZATION_ID(+)
AND HAO.ORGANIZATION_ID = HAT.ORGANIZATION_ID(+)
AND HAT.LANGUAGE(+) = USERENV ('LANG')
AND CL.CUSTOMER_TRX_ID = RCG.CUSTOMER_TRX_ID
AND CL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID
AND RCG.CODE_COMBINATION_ID = GCV_REV.CODE_COMBINATION_ID
AND MS.COST_OF_SALES_ACCOUNT = GCV_COST.CODE_COMBINATION_ID
AND RCG.ACCOUNT_CLASS = 'REV'
AND CL.INVENTORY_ITEM_ID = CSTI.INVENTORY_ITEM_ID(+)
AND CL.WAREHOUSE_ID = CSTI.ORGANIZATION_ID(+)
AND CSTI.COST_TYPE(+) = 'Average'
AND OOLA.PROJECT_ID = PP.PROJECT_ID(+)
AND OOLA.TASK_ID = PT.TASK_ID(+)
AND PT.TASK_ID = TT.PROJ_ELEMENT_ID(+)
AND MS.INVENTORY_ITEM_ID = MT.INVENTORY_ITEM_ID(+)
AND MS.ORGANIZATION_ID = MT.ORGANIZATION_ID(+)
AND MT.LANGUAGE(+) = USERENV ('LANG')
AND NVL (HCA.CUST_ACCOUNT_ID, -1) = NVL (HCS.CUST_ACCOUNT_ID, -1)
AND NVL (TRUNC (AN.CREATION_DATE), CT.TRX_DATE) BETWEEN :B4 AND :B3
AND UPPER (RC.NAME) NOT IN
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'EMR_AR_SALESREP_EXC_AUT_INV'
AND DESCRIPTION = 'AUTO INVOICE'
AND LANGUAGE = USERENV ('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN START_DATE_ACTIVE
AND NVL (END_DATE_ACTIVE, SYSDATE + 1)
AND TAG = 'HIDE')
AND OOLA.HEADER_ID = OOHA.HEADER_ID
AND OOHA.SALESREP_ID = JRS.SALESREP_ID(+)
AND OOHA.HEADER_ID = LPH.HEADER_ID(+)
AND OOLA.LINE_ID = LPL.LINE_ID(+)
AND OOHA.ORDER_SOURCE_ID = OOS.ORDER_SOURCE_ID(+)
AND OOLA.LINE_TYPE_ID = OTT.TRANSACTION_TYPE_ID
AND OTT.TRANSACTION_TYPE_CODE = 'LINE'
AND OOLA.FOB_POINT_CODE = FLV_L.LOOKUP_CODE(+)
AND FLV_L.LOOKUP_TYPE(+) = 'FOB'
AND FLV_L.VIEW_APPLICATION_ID(+) = 222
AND FLV_L.LANGUAGE(+) = USERENV ('LANG')
AND OOHA.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
AND OTTT.LANGUAGE(+) = USERENV ('LANG')
AND CT.ORG_ID = :B2
AND CL.INTERFACE_LINE_CONTEXT IN
('ORDER ENTRY', 'PROJECTS INVOICES', 'INTERCOMPANY')
AND TO_CHAR (OOLA.LINE_ID) =
DECODE (CL.INTERFACE_LINE_CONTEXT,
'ORDER ENTRY', CL.INTERFACE_LINE_ATTRIBUTE6,
'PROJECTS INVOICES', CL.ATTRIBUTE11,
'INTERCOMPANY', CL.INTERFACE_LINE_ATTRIBUTE6)
AND :B1 = 'Y'
AND CT.CUSTOMER_TRX_ID = AN.CUSTOMER_TRX_ID(+)
AND AN.NOTE_TYPE(+) = 'MAINTAIN'
AND AN.TEXT(+) = 'NOTA EM TRANSITO'
AND ( (NVL (
(SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE =
'EMR AR SALES X FUTURE DLVY BR'
AND FLV.LANGUAGE = USERENV ('LANG')
AND FLV.ENABLED_FLAG = 'Y'
AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
TRUNC (SYSDATE)
AND NVL (FLV.END_DATE_ACTIVE,
TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE)
AND FLV.MEANING = OTTT.NAME),
'N') =
'N')
OR (NVL (
(SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE =
'EMR AR SALES X FUTURE DLVY BR'
AND FLV.LANGUAGE = USERENV ('LANG')
AND FLV.ENABLED_FLAG = 'Y'
AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
TRUNC (SYSDATE)
AND NVL (FLV.END_DATE_ACTIVE,
TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE)
AND OTT.NAME LIKE FLV.LOOKUP_CODE
AND OTTT.NAME LIKE FLV.DESCRIPTION),
'Y') =
'N'))
UNION ALL
SELECT CT.TRX_NUMBER
INVOICE_NUMBER,
CT.DOC_SEQUENCE_VALUE
FISCAL_NUMBER,
CL.LINE_NUMBER
LINE_NUM,
HPA.PARTY_NAME
COMPANY_NAME,
HAT.NAME
BUSINESS_UNIT_NAME,
NULL
SIC_CODE,
NULL
SIC_DESCRIPTION,
NVL (ORD_TYPE.ORDER_NUMBER, 'Manual Invoice-AR')
SO_NUMBER,
NULL
SALESPERSON,
TO_CHAR (CL.SALES_ORDER_LINE)
SO_LINE_NUMBER,
NULL
PROJECT_NUMBER,
NULL
TASK_NUMBER,
REPLACE (
REPLACE (
REPLACE (REPLACE (CL.DESCRIPTION, ';', ','), CHR (10), NULL),
CHR (09),
NULL),
CHR (13),
NULL)
ITEM_DESCRIPTION,
CL.QUANTITY_INVOICED
QTY,
CT.TRX_DATE
INVOICE_DATE,
RC.NAME
TRANSACTION_TYPE,
CT.INVOICE_CURRENCY_CODE
FUNCTIONAL_CURRENCY_FC,
NVL (CSTI.ITEM_COST, 0)
UNIT_COST_FC,
(NVL (CSTI.ITEM_COST, 0) * NVL (CL.QUANTITY_INVOICED, 0))
TOTAL_COST_FC,
DECODE (CT.INVOICE_CURRENCY_CODE,
'BRL', CL.EXTENDED_AMOUNT,
CL.EXTENDED_AMOUNT * CT.EXCHANGE_RATE)
INVOICED_AMOUNT_FC,
HLA.LOCATION_CODE
SHIPPING_ORGANIZATION,
MS.SEGMENT1
ORDERED_ITEM,
GCV_REV.CONCATENATED_SEGMENTS
SALES_ACCOUNT,
GCV_COST.CONCATENATED_SEGMENTS
COST_OF_GOODS_SOLD_ACCOUNT,
CL.GLOBAL_ATTRIBUTE2
FISCAL_CLASSIFICATION_CODE,
NULL
MRO_PROJECT,
HLO.CITY
CUSTOMER_CITY,
CL.GLOBAL_ATTRIBUTE4
ITEM_ORIGIN,
REPLACE (
REPLACE (
REPLACE (REPLACE (MT.DESCRIPTION, ';', ','), CHR (10), NULL),
CHR (09),
NULL),
CHR (13),
NULL)
ITEM_PORTUGUESE_DESCRIPTION,
NULL
PO_RECEIVED_DATE,
CL.GLOBAL_ATTRIBUTE3
TRANSACTION_CONDITION_CLASS,
HLO.STATE
CUSTOMER_STATE,
RC.GLOBAL_ATTRIBUTE3 || '|' || RC.DESCRIPTION
TIPO_DE_FATURAMENTO,
CSTI.COST_TYPE
COST_TYPE,
HCA.ACCOUNT_NUMBER
CUSTOMER_CODE,
CT.CUSTOMER_TRX_ID,
DECODE (HCA.CUSTOMER_TYPE, 'R', 'Externo', 'I', 'Interno')
TYPE_CUSTOMER,
CT.STATUS_TRX,
NVL (ORD_TYPE.NAME, 'Manual Invoice-AR')
SO_ORDER_TYPE,
RC.TYPE
RC_TYPE,
REPLACE (
REPLACE (
REPLACE (REPLACE (CT.ATTRIBUTE1, ';', ':'), CHR (10), NULL),
CHR (13),
NULL),
CHR (09),
NULL)
FINAL_DELIVERY_DATE,
NULL
REQ_DELIVERY_DATE,
NULL
REQ_SHIP_DATE,
NULL
ORIGINAL_PROMISED_DATE,
NULL
PROMISE_DATE,
NULL
SCHEDULE_SHIP_DATE,
NULL
CUSTOMER_PO,
NULL
LINE_TYPE,
NULL
TIER_TYPE,
NULL
TASK_TYPE,
NULL
KOB_HEADER,
NULL
KOB_LINE,
NULL
SHIPPING_TERMS,
NULL
SHIPMENT_PRIORITY,
NULL
ORDER_SOURCE,
NULL
QUOTE_NUMBER,
NULL
PROJECT_TYPE,
NULL
PROJECT_NAME,
(SELECT LPAD (PERIOD_NUM, 2, '0')
FROM APPS.GL_PERIOD_STATUSES
WHERE SET_OF_BOOKS_ID IN (SELECT ATTRIBUTE1
FROM APPS.GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = 443)
AND APPLICATION_ID = 101
AND TRUNC (CT.TRX_DATE) BETWEEN TRUNC (START_DATE)
AND TRUNC (END_DATE))
PERIOD,
CL.WAREHOUSE_ID
SHIP_FROM_ORG_ID,
NULL
OE_LINE_ID,
NULL
OE_HEADER_ID,
CL.INVENTORY_ITEM_ID
INVENTORY_ITEM_ID,
CL.CUSTOMER_TRX_LINE_ID
CUSTOMER_TRX_LINE_ID,
CT.ORG_ID
ORG_ID,
CT.EXCHANGE_RATE
EXCHANGE_RATE,
CL.EXTENDED_AMOUNT
EXTENDED_AMOUNT,
GCV_REV.CODE_COMBINATION_ID
REV_CODE_COMBINATION_ID,
CL.WAREHOUSE_ID
WAREHOUSE_ID,
NULL
END_CUSTOMER_SITE_USE_ID,
(SELECT LPH1.SERVICE_CLOUD_REF
FROM APPS.XXOM_3LP_SYM_ORA_ORDER_HDR LPH1
WHERE LPH1.HEADER_ID = ORD_TYPE.HEADER_ID)
SERVICE_CLOUD_REF
FROM APPS.RA_CUSTOMER_TRX CT,
APPS.RA_CUSTOMER_TRX_LINES CL,
APPS.RA_CUST_TRX_TYPES RC,
APPS.RA_BATCH_SOURCES BS,
APPS.MTL_SYSTEM_ITEMS_B MS,
APPS.MTL_SYSTEM_ITEMS_TL MT,
APPS.CST_ITEM_COST_TYPE_V CSTI,
APPS.HR_LOCATIONS HLA,
APPS.HZ_PARTIES HPA,
APPS.HZ_CUST_ACCOUNTS HCA,
APPS.HZ_PARTY_SITES HPS,
APPS.HZ_LOCATIONS HLO,
APPS.HZ_CUST_ACCT_SITES HCS,
APPS.HZ_CUST_SITE_USES HCU,
APPS.HR_ALL_ORGANIZATION_UNITS HAO,
APPS.HR_ALL_ORGANIZATION_UNITS_TL HAT,
APPS.GL_CODE_COMBINATIONS_KFV GCV_REV,
APPS.RA_CUST_TRX_LINE_GL_DIST RCG,
APPS.GL_CODE_COMBINATIONS_KFV GCV_COST,
APPS.PA_PROJECTS PPA,
APPS.AR_NOTES AN,
(SELECT TO_CHAR (OOHA.ORDER_NUMBER) ORDER_NUMBER,
OTT.NAME,
OOHA.HEADER_ID
FROM APPS.OE_ORDER_HEADERS OOHA, APPS.OE_TRANSACTION_TYPES_TL OTT
WHERE OOHA.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
AND OTT.LANGUAGE = USERENV ('LANG')
AND OOHA.ORG_ID = :B2) ORD_TYPE
WHERE CT.SHIP_TO_SITE_USE_ID = HCU.SITE_USE_ID(+)
AND HCU.CUST_ACCT_SITE_ID = HCS.CUST_ACCT_SITE_ID(+)
AND HCS.PARTY_SITE_ID = HPS.PARTY_SITE_ID(+)
AND HPS.LOCATION_ID = HLO.LOCATION_ID(+)
AND HCS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID(+)
AND HCA.PARTY_ID = HPA.PARTY_ID(+)
AND HCU.SITE_USE_CODE(+) = 'SHIP_TO'
AND CT.CUSTOMER_TRX_ID = CL.CUSTOMER_TRX_ID
AND CL.LINE_TYPE = 'LINE'
AND CT.CUST_TRX_TYPE_ID = RC.CUST_TRX_TYPE_ID
AND RC.ORG_ID = CT.ORG_ID
AND ( (RC.TYPE = 'INV')
OR (NVL (
(SELECT DECODE (FLV.TAG, 'INCLUDE', 'Y', 'N')
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE =
'EMR AR SALES X FUTURE DLVY BR'
AND FLV.LANGUAGE = USERENV ('LANG')
AND FLV.ENABLED_FLAG = 'Y'
AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
TRUNC (SYSDATE)
AND NVL (FLV.END_DATE_ACTIVE,
TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE)
AND FLV.MEANING = RC.NAME),
'N') =
'Y'))
AND NVL (
(SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'EMR AR SALES X FUTURE DLVY BR'
AND FLV.LANGUAGE = USERENV ('LANG')
AND FLV.ENABLED_FLAG = 'Y'
AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
TRUNC (SYSDATE)
AND NVL (FLV.END_DATE_ACTIVE, TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE)
AND FLV.MEANING = RC.NAME),
'N') =
'N'
AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND BS.ORG_ID = :B2
AND BS.BATCH_SOURCE_TYPE = 'INV'
AND CL.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID(+)
AND CL.WAREHOUSE_ID = MS.ORGANIZATION_ID(+)
AND CL.WAREHOUSE_ID = HLA.INVENTORY_ORGANIZATION_ID(+)
AND HLA.INVENTORY_ORGANIZATION_ID = HAO.ORGANIZATION_ID(+)
AND HAO.ORGANIZATION_ID = HAT.ORGANIZATION_ID(+)
AND CL.SALES_ORDER = PPA.SEGMENT1(+)
AND PPA.ORG_ID(+) = :B2
AND HAT.LANGUAGE(+) = USERENV ('LANG')
AND CT.COMPLETE_FLAG = 'Y'
AND CL.CUSTOMER_TRX_ID = RCG.CUSTOMER_TRX_ID
AND CL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID
AND RCG.CODE_COMBINATION_ID = GCV_REV.CODE_COMBINATION_ID
AND MS.COST_OF_SALES_ACCOUNT = GCV_COST.CODE_COMBINATION_ID
AND RCG.ACCOUNT_CLASS = 'REV'
AND CL.INVENTORY_ITEM_ID = CSTI.INVENTORY_ITEM_ID(+)
AND CL.WAREHOUSE_ID = CSTI.ORGANIZATION_ID(+)
AND CSTI.COST_TYPE(+) = 'Average'
AND MS.INVENTORY_ITEM_ID = MT.INVENTORY_ITEM_ID(+)
AND MS.ORGANIZATION_ID = MT.ORGANIZATION_ID(+)
AND MT.LANGUAGE(+) = USERENV ('LANG')
AND CT.CT_REFERENCE = ORD_TYPE.ORDER_NUMBER(+)
AND CT.CUSTOMER_TRX_ID = AN.CUSTOMER_TRX_ID(+)
AND AN.NOTE_TYPE(+) = 'MAINTAIN'
AND AN.TEXT(+) = 'NOTA EM TRANSITO'
AND NVL (TRUNC (AN.CREATION_DATE), CT.TRX_DATE) BETWEEN :B4 AND :B3
AND NOT ( CT.STATUS_TRX = 'VD'
AND RC.GLOBAL_ATTRIBUTE3 IN
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE =
'EMR_AR_SALESREP_EXC_MAN_INV'
AND DESCRIPTION = 'CFOP'
AND LANGUAGE = USERENV ('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN START_DATE_ACTIVE
AND NVL (END_DATE_ACTIVE,
SYSDATE + 1)
AND TAG = 'HIDE'))
AND UPPER (RC.NAME) IN
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'EMR_AR_SALESREP_EXC_MAN_INV'
AND DESCRIPTION = 'MANUAL INVOICE'
AND LANGUAGE = USERENV ('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN START_DATE_ACTIVE
AND NVL (END_DATE_ACTIVE, SYSDATE + 1)
AND TAG = 'SHOW')
AND CT.ORG_ID = :B2
AND :B5 = 'Y'
ORDER BY 1, 2;


Re: Sql is causing performance issue [message #686590 is a reply to message #686589] Tue, 18 October 2022 10:51 Go to previous messageGo to next message
nishant1987
Messages: 8
Registered: September 2022
Junior Member
SELECT DISTINCT
       CT.TRX_NUMBER
           INVOICE_NUMBER,
       CT.DOC_SEQUENCE_VALUE
           FISCAL_NUMBER,
       CL.LINE_NUMBER
           LINE_NUM,
       HPA.PARTY_NAME
           COMPANY_NAME,
       HAT.NAME
           BUSINESS_UNIT_NAME,
       LPH.SIC_CODE
           SIC_CODE,
       REPLACE (LPH.SIC_DESCRIPTION, ';', ',')
           SIC_DESCRIPTION,
       TO_CHAR (OOHA.ORDER_NUMBER)
           SO_NUMBER,
       JRS.NAME
           SALESPERSON,
       TO_CHAR (OOLA.LINE_NUMBER)
           SO_LINE_NUMBER,
       PP.SEGMENT1
           PROJECT_NUMBER,
       PT.TASK_NUMBER
           TASK_NUMBER,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (CL.DESCRIPTION, ';', ','), CHR (10), NULL),
               CHR (09),
               NULL),
           CHR (13),
           NULL)
           ITEM_DESCRIPTION,
       CL.QUANTITY_INVOICED
           QTY,
       CT.TRX_DATE
           INVOICE_DATE,
       RC.NAME
           TRANSACTION_TYPE,
       CT.INVOICE_CURRENCY_CODE
           FUNCTIONAL_CURRENCY_FC,
       NVL (CSTI.ITEM_COST, 0)
           UNIT_COST_FC,
       (NVL (CSTI.ITEM_COST, 0) * NVL (CL.QUANTITY_INVOICED, 0))
           TOTAL_COST_FC,
       DECODE (CT.INVOICE_CURRENCY_CODE,
               'BRL', CL.EXTENDED_AMOUNT,
               CL.EXTENDED_AMOUNT * CT.EXCHANGE_RATE)
           INVOICED_AMOUNT_FC,
       HLA.LOCATION_CODE
           SHIPPING_ORGANIZATION,
       MS.SEGMENT1
           ORDERED_ITEM,
       GCV_REV.CONCATENATED_SEGMENTS
           SALES_ACCOUNT,
       GCV_COST.CONCATENATED_SEGMENTS
           COST_OF_GOODS_SOLD_ACCOUNT,
       CL.GLOBAL_ATTRIBUTE2
           FISCAL_CLASSIFICATION_CODE,
       PP.SEGMENT1
           MRO_PROJECT,
       HLO.CITY
           CUSTOMER_CITY,
       CL.GLOBAL_ATTRIBUTE4
           ITEM_ORIGIN,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (MT.DESCRIPTION, ';', ','), CHR (10), NULL),
               CHR (09),
               NULL),
           CHR (13),
           NULL)
           ITEM_PORTUGUESE_DESCRIPTION,
       LPH.PORECEIPTDATE
           PO_RECEIVED_DATE,
       CL.GLOBAL_ATTRIBUTE3
           TRANSACTION_CONDITION_CLASS,
       HLO.STATE
           CUSTOMER_STATE,
       RC.GLOBAL_ATTRIBUTE3 || '|' || RC.DESCRIPTION
           TIPO_DE_FATURAMENTO,
       CSTI.COST_TYPE
           COST_TYPE,
       HCA.ACCOUNT_NUMBER
           CUSTOMER_CODE,
       CT.CUSTOMER_TRX_ID,
       DECODE (HCA.CUSTOMER_TYPE,  'R', 'Externo',  'I', 'Interno')
           TYPE_CUSTOMER,
       CT.STATUS_TRX
           STATUS_TRX,
       OTTT.NAME
           SO_ORDER_TYPE,
       RC.TYPE
           RC_TYPE,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (CT.ATTRIBUTE1, ';', ':'), CHR (10), NULL),
               CHR (13),
               NULL),
           CHR (09),
           NULL)
           FINAL_DELIVERY_DATE,
       LPL.REQUESTED_DELIVERY_DATE
           REQ_DELIVERY_DATE,
       OOLA.REQUEST_DATE
           REQ_SHIP_DATE,
       LPL.ORIGINAL_PROMISE_DATE
           ORIGINAL_PROMISED_DATE,
       OOLA.PROMISE_DATE
           PROMISE_DATE,
       OOLA.SCHEDULE_SHIP_DATE
           SCHEDULE_SHIP_DATE,
       REPLACE (OOHA.CUST_PO_NUMBER, CHR (13), NULL)
           CUSTOMER_PO,
       OTT.NAME
           LINE_TYPE,
       LPH.PROJECT_TYPE
           TIER_TYPE,
       TT.TASK_TYPE
           TASK_TYPE,
       LPH.KIND_OF_BUSINESS
           KOB_HEADER,
       LPL.KOB3
           KOB_LINE,
       FLV_L.MEANING
           SHIPPING_TERMS,
       OOLA.SHIPMENT_PRIORITY_CODE
           SHIPMENT_PRIORITY,
       OOS.NAME
           ORDER_SOURCE,
       DECODE (OOHA.SOURCE_DOCUMENT_TYPE_ID,
               16, (SELECT TO_CHAR (AQH.QUOTE_NUMBER) QUOTE_NUMBER
                      FROM APPS.ASO_QUOTE_HEADERS AQH
                     WHERE AQH.QUOTE_HEADER_ID = OOHA.SOURCE_DOCUMENT_ID),
               NULL)
           QUOTE_NUMBER,
       PP.PROJECT_TYPE
           PROJECT_TYPE,
       PP.NAME
           PROJECT_NAME,
       (SELECT LPAD (PERIOD_NUM, 2, '0')
          FROM APPS.GL_PERIOD_STATUSES
         WHERE     SET_OF_BOOKS_ID IN (SELECT ATTRIBUTE1
                                         FROM APPS.GL_SETS_OF_BOOKS
                                        WHERE SET_OF_BOOKS_ID = 443)
               AND APPLICATION_ID = 101
               AND TRUNC (CT.TRX_DATE) BETWEEN TRUNC (START_DATE)
                                           AND TRUNC (END_DATE))
           PERIOD,
       OOLA.SHIP_FROM_ORG_ID
           SHIP_FROM_ORG_ID,
       OOLA.LINE_ID
           OE_LINE_ID,
       OOLA.HEADER_ID
           OE_HEADER_ID,
       OOLA.INVENTORY_ITEM_ID
           INVENTORY_ITEM_ID,
       CL.CUSTOMER_TRX_LINE_ID
           CUSTOMER_TRX_LINE_ID,
       CT.ORG_ID
           ORG_ID,
       CT.EXCHANGE_RATE
           EXCHANGE_RATE,
       EXTENDED_AMOUNT
           EXTENDED_AMOUNT,
       GCV_REV.CODE_COMBINATION_ID
           REV_CODE_COMBINATION_ID,
       CL.WAREHOUSE_ID
           WAREHOUSE_ID,
       OOHA.END_CUSTOMER_SITE_USE_ID
           END_CUSTOMER_SITE_USE_ID,
       (SELECT LPH1.SERVICE_CLOUD_REF
          FROM APPS.XXOM_3LP_SYM_ORA_ORDER_HDR LPH1
         WHERE LPH1.HEADER_ID = OOHA.HEADER_ID)
           SERVICE_CLOUD_REF
  FROM APPS.RA_CUSTOMER_TRX               CT,
       APPS.RA_CUSTOMER_TRX_LINES         CL,
       APPS.RA_CUST_TRX_TYPES             RC,
       APPS.RA_BATCH_SOURCES              BS,
       APPS.MTL_SYSTEM_ITEMS_B            MS,
       APPS.MTL_SYSTEM_ITEMS_TL           MT,
       APPS.CST_ITEM_COST_TYPE_V          CSTI,
       APPS.HR_LOCATIONS                  HLA,
       APPS.HZ_PARTIES                    HPA,
       APPS.HZ_CUST_ACCOUNTS              HCA,
       APPS.HZ_PARTY_SITES                HPS,
       APPS.HZ_LOCATIONS                  HLO,
       APPS.HZ_CUST_ACCT_SITES            HCS,
       APPS.HZ_CUST_SITE_USES             HCU,
       APPS.HR_ALL_ORGANIZATION_UNITS     HAO,
       APPS.HR_ALL_ORGANIZATION_UNITS_TL  HAT,
       APPS.GL_CODE_COMBINATIONS_KFV      GCV_REV,
       APPS.RA_CUST_TRX_LINE_GL_DIST      RCG,
       APPS.GL_CODE_COMBINATIONS_KFV      GCV_COST,
       APPS.OE_ORDER_LINES                OOLA,
       APPS.OE_ORDER_HEADERS              OOHA,
       APPS.OE_TRANSACTION_TYPES_TL       OTTT,
       APPS.PA_PROJECTS                   PP,
       APPS.PA_TASKS                      PT,
       APPS.XXOM_3LP_SYM_ORA_ORDER_LINES  LPL,
       APPS.XXOM_3LP_SYM_ORA_ORDER_HDR    LPH,
       APPS.OE_ORDER_SOURCES              OOS,
       APPS.OE_TRANSACTION_TYPES          OTT,
       APPS.FND_LOOKUP_VALUES             FLV_L,
       APPS.JTF_RS_SALESREPS              JRS,
       APPS.AR_NOTES                      AN,
       (SELECT PPE.PROJ_ELEMENT_ID, PTT.TASK_TYPE
          FROM APPS.PA_PROJ_ELEMENTS PPE, APPS.PA_TASK_TYPES PTT
         WHERE PPE.TYPE_ID = PTT.TASK_TYPE_ID) TT
 WHERE     HPA.PARTY_ID(+) = HCA.PARTY_ID
       AND HCA.PARTY_ID(+) = HPS.PARTY_ID
       AND HPS.LOCATION_ID = HLO.LOCATION_ID(+)
       AND HPS.PARTY_SITE_ID(+) = HCS.PARTY_SITE_ID
       AND HCS.CUST_ACCT_SITE_ID(+) = HCU.CUST_ACCT_SITE_ID
       AND (   (    OOS.NAME != 'Internal'
                AND HCU.SITE_USE_ID = CT.SHIP_TO_SITE_USE_ID
                AND HCU.SITE_USE_CODE = 'SHIP_TO')
            OR (    OOS.NAME = 'Internal'
                AND HCU.SITE_USE_ID = CT.BILL_TO_SITE_USE_ID
                AND HCU.SITE_USE_CODE = 'BILL_TO'))
       AND CT.CUSTOMER_TRX_ID = CL.CUSTOMER_TRX_ID
       AND CT.COMPLETE_FLAG = 'Y'
       AND CL.LINE_TYPE = 'LINE'
       AND CT.CUST_TRX_TYPE_ID = RC.CUST_TRX_TYPE_ID
       AND RC.ORG_ID = CT.ORG_ID
       AND RC.TYPE = 'INV'
       AND (   RC.ACCOUNTING_AFFECT_FLAG = 'Y'
            OR (SELECT DECODE (FLV.TAG, 'INCLUDE', 'Y', 'N')
                  FROM FND_LOOKUP_VALUES FLV
                 WHERE     FLV.LOOKUP_TYPE = 'EMR AR SALES X FUTURE DLVY BR'
                       AND FLV.LANGUAGE = USERENV ('LANG')
                       AND FLV.ENABLED_FLAG = 'Y'
                       AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
                           TRUNC (SYSDATE)
                       AND NVL (FLV.END_DATE_ACTIVE, TRUNC (SYSDATE) + 1) >
                           TRUNC (SYSDATE)
                       AND FLV.MEANING = RC.NAME) =
               'Y')
       AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
       AND BS.ORG_ID = CT.ORG_ID
       AND BS.BATCH_SOURCE_TYPE = 'FOREIGN'
       AND CL.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID(+)
       AND CL.WAREHOUSE_ID = MS.ORGANIZATION_ID(+)
       AND CL.WAREHOUSE_ID = HLA.INVENTORY_ORGANIZATION_ID(+)
       AND HLA.INVENTORY_ORGANIZATION_ID = HAO.ORGANIZATION_ID(+)
       AND HAO.ORGANIZATION_ID = HAT.ORGANIZATION_ID(+)
       AND HAT.LANGUAGE(+) = USERENV ('LANG')
       AND CL.CUSTOMER_TRX_ID = RCG.CUSTOMER_TRX_ID
       AND CL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID
       AND RCG.CODE_COMBINATION_ID = GCV_REV.CODE_COMBINATION_ID
       AND MS.COST_OF_SALES_ACCOUNT = GCV_COST.CODE_COMBINATION_ID
       AND RCG.ACCOUNT_CLASS = 'REV'
       AND CL.INVENTORY_ITEM_ID = CSTI.INVENTORY_ITEM_ID(+)
       AND CL.WAREHOUSE_ID = CSTI.ORGANIZATION_ID(+)
       AND CSTI.COST_TYPE(+) = 'Average'
       AND OOLA.PROJECT_ID = PP.PROJECT_ID(+)
       AND OOLA.TASK_ID = PT.TASK_ID(+)
       AND PT.TASK_ID = TT.PROJ_ELEMENT_ID(+)
       AND MS.INVENTORY_ITEM_ID = MT.INVENTORY_ITEM_ID(+)
       AND MS.ORGANIZATION_ID = MT.ORGANIZATION_ID(+)
       AND MT.LANGUAGE(+) = USERENV ('LANG')
       AND NVL (HCA.CUST_ACCOUNT_ID, -1) = NVL (HCS.CUST_ACCOUNT_ID, -1)
       AND NVL (TRUNC (AN.CREATION_DATE), CT.TRX_DATE) BETWEEN :B4 AND :B3
       AND UPPER (RC.NAME) NOT IN
               (SELECT LOOKUP_CODE
                  FROM FND_LOOKUP_VALUES
                 WHERE     LOOKUP_TYPE = 'EMR_AR_SALESREP_EXC_AUT_INV'
                       AND DESCRIPTION = 'AUTO INVOICE'
                       AND LANGUAGE = USERENV ('LANG')
                       AND ENABLED_FLAG = 'Y'
                       AND SYSDATE BETWEEN START_DATE_ACTIVE
                                       AND NVL (END_DATE_ACTIVE, SYSDATE + 1)
                       AND TAG = 'HIDE')
       AND OOLA.HEADER_ID = OOHA.HEADER_ID
       AND OOHA.SALESREP_ID = JRS.SALESREP_ID(+)
       AND OOHA.HEADER_ID = LPH.HEADER_ID(+)
       AND OOLA.LINE_ID = LPL.LINE_ID(+)
       AND OOHA.ORDER_SOURCE_ID = OOS.ORDER_SOURCE_ID(+)
       AND OOLA.LINE_TYPE_ID = OTT.TRANSACTION_TYPE_ID
       AND OTT.TRANSACTION_TYPE_CODE = 'LINE'
       AND OOLA.FOB_POINT_CODE = FLV_L.LOOKUP_CODE(+)
       AND FLV_L.LOOKUP_TYPE(+) = 'FOB'
       AND FLV_L.VIEW_APPLICATION_ID(+) = 222
       AND FLV_L.LANGUAGE(+) = USERENV ('LANG')
       AND OOHA.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
       AND OTTT.LANGUAGE(+) = USERENV ('LANG')
       AND CT.ORG_ID = :B2
       AND CL.INTERFACE_LINE_CONTEXT IN
               ('ORDER ENTRY', 'PROJECTS INVOICES', 'INTERCOMPANY')
       AND TO_CHAR (OOLA.LINE_ID) =
           DECODE (CL.INTERFACE_LINE_CONTEXT,
                   'ORDER ENTRY', CL.INTERFACE_LINE_ATTRIBUTE6,
                   'PROJECTS INVOICES', CL.ATTRIBUTE11,
                   'INTERCOMPANY', CL.INTERFACE_LINE_ATTRIBUTE6)
       AND :B1 = 'Y'
       AND CT.CUSTOMER_TRX_ID = AN.CUSTOMER_TRX_ID(+)
       AND AN.NOTE_TYPE(+) = 'MAINTAIN'
       AND AN.TEXT(+) = 'NOTA EM TRANSITO'
       AND (   (NVL (
                    (SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
                       FROM FND_LOOKUP_VALUES FLV
                      WHERE     FLV.LOOKUP_TYPE =
                                'EMR AR SALES X FUTURE DLVY BR'
                            AND FLV.LANGUAGE = USERENV ('LANG')
                            AND FLV.ENABLED_FLAG = 'Y'
                            AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
                                TRUNC (SYSDATE)
                            AND NVL (FLV.END_DATE_ACTIVE,
                                     TRUNC (SYSDATE) + 1) >
                                TRUNC (SYSDATE)
                            AND FLV.MEANING = OTTT.NAME),
                    'N') =
                'N')
            OR (NVL (
                    (SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
                       FROM FND_LOOKUP_VALUES FLV
                      WHERE     FLV.LOOKUP_TYPE =
                                'EMR AR SALES X FUTURE DLVY BR'
                            AND FLV.LANGUAGE = USERENV ('LANG')
                            AND FLV.ENABLED_FLAG = 'Y'
                            AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
                                TRUNC (SYSDATE)
                            AND NVL (FLV.END_DATE_ACTIVE,
                                     TRUNC (SYSDATE) + 1) >
                                TRUNC (SYSDATE)
                            AND OTT.NAME LIKE FLV.LOOKUP_CODE
                            AND OTTT.NAME LIKE FLV.DESCRIPTION),
                    'Y') =
                'N'))
UNION ALL
SELECT CT.TRX_NUMBER
           INVOICE_NUMBER,
       CT.DOC_SEQUENCE_VALUE
           FISCAL_NUMBER,
       CL.LINE_NUMBER
           LINE_NUM,
       HPA.PARTY_NAME
           COMPANY_NAME,
       HAT.NAME
           BUSINESS_UNIT_NAME,
       NULL
           SIC_CODE,
       NULL
           SIC_DESCRIPTION,
       NVL (ORD_TYPE.ORDER_NUMBER, 'Manual Invoice-AR')
           SO_NUMBER,
       NULL
           SALESPERSON,
       TO_CHAR (CL.SALES_ORDER_LINE)
           SO_LINE_NUMBER,
       NULL
           PROJECT_NUMBER,
       NULL
           TASK_NUMBER,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (CL.DESCRIPTION, ';', ','), CHR (10), NULL),
               CHR (09),
               NULL),
           CHR (13),
           NULL)
           ITEM_DESCRIPTION,
       CL.QUANTITY_INVOICED
           QTY,
       CT.TRX_DATE
           INVOICE_DATE,
       RC.NAME
           TRANSACTION_TYPE,
       CT.INVOICE_CURRENCY_CODE
           FUNCTIONAL_CURRENCY_FC,
       NVL (CSTI.ITEM_COST, 0)
           UNIT_COST_FC,
       (NVL (CSTI.ITEM_COST, 0) * NVL (CL.QUANTITY_INVOICED, 0))
           TOTAL_COST_FC,
       DECODE (CT.INVOICE_CURRENCY_CODE,
               'BRL', CL.EXTENDED_AMOUNT,
               CL.EXTENDED_AMOUNT * CT.EXCHANGE_RATE)
           INVOICED_AMOUNT_FC,
       HLA.LOCATION_CODE
           SHIPPING_ORGANIZATION,
       MS.SEGMENT1
           ORDERED_ITEM,
       GCV_REV.CONCATENATED_SEGMENTS
           SALES_ACCOUNT,
       GCV_COST.CONCATENATED_SEGMENTS
           COST_OF_GOODS_SOLD_ACCOUNT,
       CL.GLOBAL_ATTRIBUTE2
           FISCAL_CLASSIFICATION_CODE,
       NULL
           MRO_PROJECT,
       HLO.CITY
           CUSTOMER_CITY,
       CL.GLOBAL_ATTRIBUTE4
           ITEM_ORIGIN,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (MT.DESCRIPTION, ';', ','), CHR (10), NULL),
               CHR (09),
               NULL),
           CHR (13),
           NULL)
           ITEM_PORTUGUESE_DESCRIPTION,
       NULL
           PO_RECEIVED_DATE,
       CL.GLOBAL_ATTRIBUTE3
           TRANSACTION_CONDITION_CLASS,
       HLO.STATE
           CUSTOMER_STATE,
       RC.GLOBAL_ATTRIBUTE3 || '|' || RC.DESCRIPTION
           TIPO_DE_FATURAMENTO,
       CSTI.COST_TYPE
           COST_TYPE,
       HCA.ACCOUNT_NUMBER
           CUSTOMER_CODE,
       CT.CUSTOMER_TRX_ID,
       DECODE (HCA.CUSTOMER_TYPE,  'R', 'Externo',  'I', 'Interno')
           TYPE_CUSTOMER,
       CT.STATUS_TRX,
       NVL (ORD_TYPE.NAME, 'Manual Invoice-AR')
           SO_ORDER_TYPE,
       RC.TYPE
           RC_TYPE,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (CT.ATTRIBUTE1, ';', ':'), CHR (10), NULL),
               CHR (13),
               NULL),
           CHR (09),
           NULL)
           FINAL_DELIVERY_DATE,
       NULL
           REQ_DELIVERY_DATE,
       NULL
           REQ_SHIP_DATE,
       NULL
           ORIGINAL_PROMISED_DATE,
       NULL
           PROMISE_DATE,
       NULL
           SCHEDULE_SHIP_DATE,
       NULL
           CUSTOMER_PO,
       NULL
           LINE_TYPE,
       NULL
           TIER_TYPE,
       NULL
           TASK_TYPE,
       NULL
           KOB_HEADER,
       NULL
           KOB_LINE,
       NULL
           SHIPPING_TERMS,
       NULL
           SHIPMENT_PRIORITY,
       NULL
           ORDER_SOURCE,
       NULL
           QUOTE_NUMBER,
       NULL
           PROJECT_TYPE,
       NULL
           PROJECT_NAME,
       (SELECT LPAD (PERIOD_NUM, 2, '0')
          FROM APPS.GL_PERIOD_STATUSES
         WHERE     SET_OF_BOOKS_ID IN (SELECT ATTRIBUTE1
                                         FROM APPS.GL_SETS_OF_BOOKS
                                        WHERE SET_OF_BOOKS_ID = 443)
               AND APPLICATION_ID = 101
               AND TRUNC (CT.TRX_DATE) BETWEEN TRUNC (START_DATE)
                                           AND TRUNC (END_DATE))
           PERIOD,
       CL.WAREHOUSE_ID
           SHIP_FROM_ORG_ID,
       NULL
           OE_LINE_ID,
       NULL
           OE_HEADER_ID,
       CL.INVENTORY_ITEM_ID
           INVENTORY_ITEM_ID,
       CL.CUSTOMER_TRX_LINE_ID
           CUSTOMER_TRX_LINE_ID,
       CT.ORG_ID
           ORG_ID,
       CT.EXCHANGE_RATE
           EXCHANGE_RATE,
       CL.EXTENDED_AMOUNT
           EXTENDED_AMOUNT,
       GCV_REV.CODE_COMBINATION_ID
           REV_CODE_COMBINATION_ID,
       CL.WAREHOUSE_ID
           WAREHOUSE_ID,
       NULL
           END_CUSTOMER_SITE_USE_ID,
       (SELECT LPH1.SERVICE_CLOUD_REF
          FROM APPS.XXOM_3LP_SYM_ORA_ORDER_HDR LPH1
         WHERE LPH1.HEADER_ID = ORD_TYPE.HEADER_ID)
           SERVICE_CLOUD_REF
  FROM APPS.RA_CUSTOMER_TRX               CT,
       APPS.RA_CUSTOMER_TRX_LINES         CL,
       APPS.RA_CUST_TRX_TYPES             RC,
       APPS.RA_BATCH_SOURCES              BS,
       APPS.MTL_SYSTEM_ITEMS_B            MS,
       APPS.MTL_SYSTEM_ITEMS_TL           MT,
       APPS.CST_ITEM_COST_TYPE_V          CSTI,
       APPS.HR_LOCATIONS                  HLA,
       APPS.HZ_PARTIES                    HPA,
       APPS.HZ_CUST_ACCOUNTS              HCA,
       APPS.HZ_PARTY_SITES                HPS,
       APPS.HZ_LOCATIONS                  HLO,
       APPS.HZ_CUST_ACCT_SITES            HCS,
       APPS.HZ_CUST_SITE_USES             HCU,
       APPS.HR_ALL_ORGANIZATION_UNITS     HAO,
       APPS.HR_ALL_ORGANIZATION_UNITS_TL  HAT,
       APPS.GL_CODE_COMBINATIONS_KFV      GCV_REV,
       APPS.RA_CUST_TRX_LINE_GL_DIST      RCG,
       APPS.GL_CODE_COMBINATIONS_KFV      GCV_COST,
       APPS.PA_PROJECTS                   PPA,
       APPS.AR_NOTES                      AN,
       (SELECT TO_CHAR (OOHA.ORDER_NUMBER) ORDER_NUMBER,
               OTT.NAME,
               OOHA.HEADER_ID
          FROM APPS.OE_ORDER_HEADERS OOHA, APPS.OE_TRANSACTION_TYPES_TL OTT
         WHERE     OOHA.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
               AND OTT.LANGUAGE = USERENV ('LANG')
               AND OOHA.ORG_ID = :B2) ORD_TYPE
 WHERE     CT.SHIP_TO_SITE_USE_ID = HCU.SITE_USE_ID(+)
       AND HCU.CUST_ACCT_SITE_ID = HCS.CUST_ACCT_SITE_ID(+)
       AND HCS.PARTY_SITE_ID = HPS.PARTY_SITE_ID(+)
       AND HPS.LOCATION_ID = HLO.LOCATION_ID(+)
       AND HCS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID(+)
       AND HCA.PARTY_ID = HPA.PARTY_ID(+)
       AND HCU.SITE_USE_CODE(+) = 'SHIP_TO'
       AND CT.CUSTOMER_TRX_ID = CL.CUSTOMER_TRX_ID
       AND CL.LINE_TYPE = 'LINE'
       AND CT.CUST_TRX_TYPE_ID = RC.CUST_TRX_TYPE_ID
       AND RC.ORG_ID = CT.ORG_ID
       AND (   (RC.TYPE = 'INV')
            OR (NVL (
                    (SELECT DECODE (FLV.TAG, 'INCLUDE', 'Y', 'N')
                       FROM FND_LOOKUP_VALUES FLV
                      WHERE     FLV.LOOKUP_TYPE =
                                'EMR AR SALES X FUTURE DLVY BR'
                            AND FLV.LANGUAGE = USERENV ('LANG')
                            AND FLV.ENABLED_FLAG = 'Y'
                            AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
                                TRUNC (SYSDATE)
                            AND NVL (FLV.END_DATE_ACTIVE,
                                     TRUNC (SYSDATE) + 1) >
                                TRUNC (SYSDATE)
                            AND FLV.MEANING = RC.NAME),
                    'N') =
                'Y'))
       AND NVL (
               (SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
                  FROM FND_LOOKUP_VALUES FLV
                 WHERE     FLV.LOOKUP_TYPE = 'EMR AR SALES X FUTURE DLVY BR'
                       AND FLV.LANGUAGE = USERENV ('LANG')
                       AND FLV.ENABLED_FLAG = 'Y'
                       AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
                           TRUNC (SYSDATE)
                       AND NVL (FLV.END_DATE_ACTIVE, TRUNC (SYSDATE) + 1) >
                           TRUNC (SYSDATE)
                       AND FLV.MEANING = RC.NAME),
               'N') =
           'N'
       AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
       AND BS.ORG_ID = :B2
       AND BS.BATCH_SOURCE_TYPE = 'INV'
       AND CL.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID(+)
       AND CL.WAREHOUSE_ID = MS.ORGANIZATION_ID(+)
       AND CL.WAREHOUSE_ID = HLA.INVENTORY_ORGANIZATION_ID(+)
       AND HLA.INVENTORY_ORGANIZATION_ID = HAO.ORGANIZATION_ID(+)
       AND HAO.ORGANIZATION_ID = HAT.ORGANIZATION_ID(+)
       AND CL.SALES_ORDER = PPA.SEGMENT1(+)
       AND PPA.ORG_ID(+) = :B2
       AND HAT.LANGUAGE(+) = USERENV ('LANG')
       AND CT.COMPLETE_FLAG = 'Y'
       AND CL.CUSTOMER_TRX_ID = RCG.CUSTOMER_TRX_ID
       AND CL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID
       AND RCG.CODE_COMBINATION_ID = GCV_REV.CODE_COMBINATION_ID
       AND MS.COST_OF_SALES_ACCOUNT = GCV_COST.CODE_COMBINATION_ID
       AND RCG.ACCOUNT_CLASS = 'REV'
       AND CL.INVENTORY_ITEM_ID = CSTI.INVENTORY_ITEM_ID(+)
       AND CL.WAREHOUSE_ID = CSTI.ORGANIZATION_ID(+)
       AND CSTI.COST_TYPE(+) = 'Average'
       AND MS.INVENTORY_ITEM_ID = MT.INVENTORY_ITEM_ID(+)
       AND MS.ORGANIZATION_ID = MT.ORGANIZATION_ID(+)
       AND MT.LANGUAGE(+) = USERENV ('LANG')
       AND CT.CT_REFERENCE = ORD_TYPE.ORDER_NUMBER(+)
       AND CT.CUSTOMER_TRX_ID = AN.CUSTOMER_TRX_ID(+)
       AND AN.NOTE_TYPE(+) = 'MAINTAIN'
       AND AN.TEXT(+) = 'NOTA EM TRANSITO'
       AND NVL (TRUNC (AN.CREATION_DATE), CT.TRX_DATE) BETWEEN :B4 AND :B3
       AND NOT (    CT.STATUS_TRX = 'VD'
                AND RC.GLOBAL_ATTRIBUTE3 IN
                        (SELECT LOOKUP_CODE
                           FROM FND_LOOKUP_VALUES
                          WHERE     LOOKUP_TYPE =
                                    'EMR_AR_SALESREP_EXC_MAN_INV'
                                AND DESCRIPTION = 'CFOP'
                                AND LANGUAGE = USERENV ('LANG')
                                AND ENABLED_FLAG = 'Y'
                                AND SYSDATE BETWEEN START_DATE_ACTIVE
                                                AND NVL (END_DATE_ACTIVE,
                                                         SYSDATE + 1)
                                AND TAG = 'HIDE'))
       AND UPPER (RC.NAME) IN
               (SELECT LOOKUP_CODE
                  FROM FND_LOOKUP_VALUES
                 WHERE     LOOKUP_TYPE = 'EMR_AR_SALESREP_EXC_MAN_INV'
                       AND DESCRIPTION = 'MANUAL INVOICE'
                       AND LANGUAGE = USERENV ('LANG')
                       AND ENABLED_FLAG = 'Y'
                       AND SYSDATE BETWEEN START_DATE_ACTIVE
                                       AND NVL (END_DATE_ACTIVE, SYSDATE + 1)
                       AND TAG = 'SHOW')
       AND CT.ORG_ID = :B2
       AND :B5 = 'Y'
ORDER BY 1, 2;
Sql behaving badly after upgrade [message #686591 is a reply to message #686589] Tue, 18 October 2022 10:54 Go to previous messageGo to next message
nishant1987
Messages: 8
Registered: September 2022
Junior Member
One of the sql is causing performance issue after 19c upgrade.

[code]

SELECT DISTINCT
CT.TRX_NUMBER
INVOICE_NUMBER,
CT.DOC_SEQUENCE_VALUE
FISCAL_NUMBER,
CL.LINE_NUMBER
LINE_NUM,
HPA.PARTY_NAME
COMPANY_NAME,
HAT.NAME
BUSINESS_UNIT_NAME,
LPH.SIC_CODE
SIC_CODE,
REPLACE (LPH.SIC_DESCRIPTION, ';', ',')
SIC_DESCRIPTION,
TO_CHAR (OOHA.ORDER_NUMBER)
SO_NUMBER,
JRS.NAME
SALESPERSON,
TO_CHAR (OOLA.LINE_NUMBER)
SO_LINE_NUMBER,
PP.SEGMENT1
PROJECT_NUMBER,
PT.TASK_NUMBER
TASK_NUMBER,
REPLACE (
REPLACE (
REPLACE (REPLACE (CL.DESCRIPTION, ';', ','), CHR (10), NULL),
CHR (09),
NULL),
CHR (13),
NULL)
ITEM_DESCRIPTION,
CL.QUANTITY_INVOICED
QTY,
CT.TRX_DATE
INVOICE_DATE,
RC.NAME
TRANSACTION_TYPE,
CT.INVOICE_CURRENCY_CODE
FUNCTIONAL_CURRENCY_FC,
NVL (CSTI.ITEM_COST, 0)
UNIT_COST_FC,
(NVL (CSTI.ITEM_COST, 0) * NVL (CL.QUANTITY_INVOICED, 0))
TOTAL_COST_FC,
DECODE (CT.INVOICE_CURRENCY_CODE,
'BRL', CL.EXTENDED_AMOUNT,
CL.EXTENDED_AMOUNT * CT.EXCHANGE_RATE)
INVOICED_AMOUNT_FC,
HLA.LOCATION_CODE
SHIPPING_ORGANIZATION,
MS.SEGMENT1
ORDERED_ITEM,
GCV_REV.CONCATENATED_SEGMENTS
SALES_ACCOUNT,
GCV_COST.CONCATENATED_SEGMENTS
COST_OF_GOODS_SOLD_ACCOUNT,
CL.GLOBAL_ATTRIBUTE2
FISCAL_CLASSIFICATION_CODE,
PP.SEGMENT1
MRO_PROJECT,
HLO.CITY
CUSTOMER_CITY,
CL.GLOBAL_ATTRIBUTE4
ITEM_ORIGIN,
REPLACE (
REPLACE (
REPLACE (REPLACE (MT.DESCRIPTION, ';', ','), CHR (10), NULL),
CHR (09),
NULL),
CHR (13),
NULL)
ITEM_PORTUGUESE_DESCRIPTION,
LPH.PORECEIPTDATE
PO_RECEIVED_DATE,
CL.GLOBAL_ATTRIBUTE3
TRANSACTION_CONDITION_CLASS,
HLO.STATE
CUSTOMER_STATE,
RC.GLOBAL_ATTRIBUTE3 || '|' || RC.DESCRIPTION
TIPO_DE_FATURAMENTO,
CSTI.COST_TYPE
COST_TYPE,
HCA.ACCOUNT_NUMBER
CUSTOMER_CODE,
CT.CUSTOMER_TRX_ID,
DECODE (HCA.CUSTOMER_TYPE, 'R', 'Externo', 'I', 'Interno')
TYPE_CUSTOMER,
CT.STATUS_TRX
STATUS_TRX,
OTTT.NAME
SO_ORDER_TYPE,
RC.TYPE
RC_TYPE,
REPLACE (
REPLACE (
REPLACE (REPLACE (CT.ATTRIBUTE1, ';', ':'), CHR (10), NULL),
CHR (13),
NULL),
CHR (09),
NULL)
FINAL_DELIVERY_DATE,
LPL.REQUESTED_DELIVERY_DATE
REQ_DELIVERY_DATE,
OOLA.REQUEST_DATE
REQ_SHIP_DATE,
LPL.ORIGINAL_PROMISE_DATE
ORIGINAL_PROMISED_DATE,
OOLA.PROMISE_DATE
PROMISE_DATE,
OOLA.SCHEDULE_SHIP_DATE
SCHEDULE_SHIP_DATE,
REPLACE (OOHA.CUST_PO_NUMBER, CHR (13), NULL)
CUSTOMER_PO,
OTT.NAME
LINE_TYPE,
LPH.PROJECT_TYPE
TIER_TYPE,
TT.TASK_TYPE
TASK_TYPE,
LPH.KIND_OF_BUSINESS
KOB_HEADER,
LPL.KOB3
KOB_LINE,
FLV_L.MEANING
SHIPPING_TERMS,
OOLA.SHIPMENT_PRIORITY_CODE
SHIPMENT_PRIORITY,
OOS.NAME
ORDER_SOURCE,
DECODE (OOHA.SOURCE_DOCUMENT_TYPE_ID,
16, (SELECT TO_CHAR (AQH.QUOTE_NUMBER) QUOTE_NUMBER
FROM APPS.ASO_QUOTE_HEADERS AQH
WHERE AQH.QUOTE_HEADER_ID = OOHA.SOURCE_DOCUMENT_ID),
NULL)
QUOTE_NUMBER,
PP.PROJECT_TYPE
PROJECT_TYPE,
PP.NAME
PROJECT_NAME,
(SELECT LPAD (PERIOD_NUM, 2, '0')
FROM APPS.GL_PERIOD_STATUSES
WHERE SET_OF_BOOKS_ID IN (SELECT ATTRIBUTE1
FROM APPS.GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = 443)
AND APPLICATION_ID = 101
AND TRUNC (CT.TRX_DATE) BETWEEN TRUNC (START_DATE)
AND TRUNC (END_DATE))
PERIOD,
OOLA.SHIP_FROM_ORG_ID
SHIP_FROM_ORG_ID,
OOLA.LINE_ID
OE_LINE_ID,
OOLA.HEADER_ID
OE_HEADER_ID,
OOLA.INVENTORY_ITEM_ID
INVENTORY_ITEM_ID,
CL.CUSTOMER_TRX_LINE_ID
CUSTOMER_TRX_LINE_ID,
CT.ORG_ID
ORG_ID,
CT.EXCHANGE_RATE
EXCHANGE_RATE,
EXTENDED_AMOUNT
EXTENDED_AMOUNT,
GCV_REV.CODE_COMBINATION_ID
REV_CODE_COMBINATION_ID,
CL.WAREHOUSE_ID
WAREHOUSE_ID,
OOHA.END_CUSTOMER_SITE_USE_ID
END_CUSTOMER_SITE_USE_ID,
(SELECT LPH1.SERVICE_CLOUD_REF
FROM APPS.XXOM_3LP_SYM_ORA_ORDER_HDR LPH1
WHERE LPH1.HEADER_ID = OOHA.HEADER_ID)
SERVICE_CLOUD_REF
FROM APPS.RA_CUSTOMER_TRX CT,
APPS.RA_CUSTOMER_TRX_LINES CL,
APPS.RA_CUST_TRX_TYPES RC,
APPS.RA_BATCH_SOURCES BS,
APPS.MTL_SYSTEM_ITEMS_B MS,
APPS.MTL_SYSTEM_ITEMS_TL MT,
APPS.CST_ITEM_COST_TYPE_V CSTI,
APPS.HR_LOCATIONS HLA,
APPS.HZ_PARTIES HPA,
APPS.HZ_CUST_ACCOUNTS HCA,
APPS.HZ_PARTY_SITES HPS,
APPS.HZ_LOCATIONS HLO,
APPS.HZ_CUST_ACCT_SITES HCS,
APPS.HZ_CUST_SITE_USES HCU,
APPS.HR_ALL_ORGANIZATION_UNITS HAO,
APPS.HR_ALL_ORGANIZATION_UNITS_TL HAT,
APPS.GL_CODE_COMBINATIONS_KFV GCV_REV,
APPS.RA_CUST_TRX_LINE_GL_DIST RCG,
APPS.GL_CODE_COMBINATIONS_KFV GCV_COST,
APPS.OE_ORDER_LINES OOLA,
APPS.OE_ORDER_HEADERS OOHA,
APPS.OE_TRANSACTION_TYPES_TL OTTT,
APPS.PA_PROJECTS PP,
APPS.PA_TASKS PT,
APPS.XXOM_3LP_SYM_ORA_ORDER_LINES LPL,
APPS.XXOM_3LP_SYM_ORA_ORDER_HDR LPH,
APPS.OE_ORDER_SOURCES OOS,
APPS.OE_TRANSACTION_TYPES OTT,
APPS.FND_LOOKUP_VALUES FLV_L,
APPS.JTF_RS_SALESREPS JRS,
APPS.AR_NOTES AN,
(SELECT PPE.PROJ_ELEMENT_ID, PTT.TASK_TYPE
FROM APPS.PA_PROJ_ELEMENTS PPE, APPS.PA_TASK_TYPES PTT
WHERE PPE.TYPE_ID = PTT.TASK_TYPE_ID) TT
WHERE HPA.PARTY_ID(+) = HCA.PARTY_ID
AND HCA.PARTY_ID(+) = HPS.PARTY_ID
AND HPS.LOCATION_ID = HLO.LOCATION_ID(+)
AND HPS.PARTY_SITE_ID(+) = HCS.PARTY_SITE_ID
AND HCS.CUST_ACCT_SITE_ID(+) = HCU.CUST_ACCT_SITE_ID
AND ( ( OOS.NAME != 'Internal'
AND HCU.SITE_USE_ID = CT.SHIP_TO_SITE_USE_ID
AND HCU.SITE_USE_CODE = 'SHIP_TO')
OR ( OOS.NAME = 'Internal'
AND HCU.SITE_USE_ID = CT.BILL_TO_SITE_USE_ID
AND HCU.SITE_USE_CODE = 'BILL_TO'))
AND CT.CUSTOMER_TRX_ID = CL.CUSTOMER_TRX_ID
AND CT.COMPLETE_FLAG = 'Y'
AND CL.LINE_TYPE = 'LINE'
AND CT.CUST_TRX_TYPE_ID = RC.CUST_TRX_TYPE_ID
AND RC.ORG_ID = CT.ORG_ID
AND RC.TYPE = 'INV'
AND ( RC.ACCOUNTING_AFFECT_FLAG = 'Y'
OR (SELECT DECODE (FLV.TAG, 'INCLUDE', 'Y', 'N')
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'EMR AR SALES X FUTURE DLVY BR'
AND FLV.LANGUAGE = USERENV ('LANG')
AND FLV.ENABLED_FLAG = 'Y'
AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
TRUNC (SYSDATE)
AND NVL (FLV.END_DATE_ACTIVE, TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE)
AND FLV.MEANING = RC.NAME) =
'Y')
AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND BS.ORG_ID = CT.ORG_ID
AND BS.BATCH_SOURCE_TYPE = 'FOREIGN'
AND CL.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID(+)
AND CL.WAREHOUSE_ID = MS.ORGANIZATION_ID(+)
AND CL.WAREHOUSE_ID = HLA.INVENTORY_ORGANIZATION_ID(+)
AND HLA.INVENTORY_ORGANIZATION_ID = HAO.ORGANIZATION_ID(+)
AND HAO.ORGANIZATION_ID = HAT.ORGANIZATION_ID(+)
AND HAT.LANGUAGE(+) = USERENV ('LANG')
AND CL.CUSTOMER_TRX_ID = RCG.CUSTOMER_TRX_ID
AND CL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID
AND RCG.CODE_COMBINATION_ID = GCV_REV.CODE_COMBINATION_ID
AND MS.COST_OF_SALES_ACCOUNT = GCV_COST.CODE_COMBINATION_ID
AND RCG.ACCOUNT_CLASS = 'REV'
AND CL.INVENTORY_ITEM_ID = CSTI.INVENTORY_ITEM_ID(+)
AND CL.WAREHOUSE_ID = CSTI.ORGANIZATION_ID(+)
AND CSTI.COST_TYPE(+) = 'Average'
AND OOLA.PROJECT_ID = PP.PROJECT_ID(+)
AND OOLA.TASK_ID = PT.TASK_ID(+)
AND PT.TASK_ID = TT.PROJ_ELEMENT_ID(+)
AND MS.INVENTORY_ITEM_ID = MT.INVENTORY_ITEM_ID(+)
AND MS.ORGANIZATION_ID = MT.ORGANIZATION_ID(+)
AND MT.LANGUAGE(+) = USERENV ('LANG')
AND NVL (HCA.CUST_ACCOUNT_ID, -1) = NVL (HCS.CUST_ACCOUNT_ID, -1)
AND NVL (TRUNC (AN.CREATION_DATE), CT.TRX_DATE) BETWEEN :B4 AND :B3
AND UPPER (RC.NAME) NOT IN
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'EMR_AR_SALESREP_EXC_AUT_INV'
AND DESCRIPTION = 'AUTO INVOICE'
AND LANGUAGE = USERENV ('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN START_DATE_ACTIVE
AND NVL (END_DATE_ACTIVE, SYSDATE + 1)
AND TAG = 'HIDE')
AND OOLA.HEADER_ID = OOHA.HEADER_ID
AND OOHA.SALESREP_ID = JRS.SALESREP_ID(+)
AND OOHA.HEADER_ID = LPH.HEADER_ID(+)
AND OOLA.LINE_ID = LPL.LINE_ID(+)
AND OOHA.ORDER_SOURCE_ID = OOS.ORDER_SOURCE_ID(+)
AND OOLA.LINE_TYPE_ID = OTT.TRANSACTION_TYPE_ID
AND OTT.TRANSACTION_TYPE_CODE = 'LINE'
AND OOLA.FOB_POINT_CODE = FLV_L.LOOKUP_CODE(+)
AND FLV_L.LOOKUP_TYPE(+) = 'FOB'
AND FLV_L.VIEW_APPLICATION_ID(+) = 222
AND FLV_L.LANGUAGE(+) = USERENV ('LANG')
AND OOHA.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
AND OTTT.LANGUAGE(+) = USERENV ('LANG')
AND CT.ORG_ID = :B2
AND CL.INTERFACE_LINE_CONTEXT IN
('ORDER ENTRY', 'PROJECTS INVOICES', 'INTERCOMPANY')
AND TO_CHAR (OOLA.LINE_ID) =
DECODE (CL.INTERFACE_LINE_CONTEXT,
'ORDER ENTRY', CL.INTERFACE_LINE_ATTRIBUTE6,
'PROJECTS INVOICES', CL.ATTRIBUTE11,
'INTERCOMPANY', CL.INTERFACE_LINE_ATTRIBUTE6)
AND :B1 = 'Y'
AND CT.CUSTOMER_TRX_ID = AN.CUSTOMER_TRX_ID(+)
AND AN.NOTE_TYPE(+) = 'MAINTAIN'
AND AN.TEXT(+) = 'NOTA EM TRANSITO'
AND ( (NVL (
(SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE =
'EMR AR SALES X FUTURE DLVY BR'
AND FLV.LANGUAGE = USERENV ('LANG')
AND FLV.ENABLED_FLAG = 'Y'
AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
TRUNC (SYSDATE)
AND NVL (FLV.END_DATE_ACTIVE,
TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE)
AND FLV.MEANING = OTTT.NAME),
'N') =
'N')
OR (NVL (
(SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE =
'EMR AR SALES X FUTURE DLVY BR'
AND FLV.LANGUAGE = USERENV ('LANG')
AND FLV.ENABLED_FLAG = 'Y'
AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
TRUNC (SYSDATE)
AND NVL (FLV.END_DATE_ACTIVE,
TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE)
AND OTT.NAME LIKE FLV.LOOKUP_CODE
AND OTTT.NAME LIKE FLV.DESCRIPTION),
'Y') =
'N'))
UNION ALL
SELECT CT.TRX_NUMBER
INVOICE_NUMBER,
CT.DOC_SEQUENCE_VALUE
FISCAL_NUMBER,
CL.LINE_NUMBER
LINE_NUM,
HPA.PARTY_NAME
COMPANY_NAME,
HAT.NAME
BUSINESS_UNIT_NAME,
NULL
SIC_CODE,
NULL
SIC_DESCRIPTION,
NVL (ORD_TYPE.ORDER_NUMBER, 'Manual Invoice-AR')
SO_NUMBER,
NULL
SALESPERSON,
TO_CHAR (CL.SALES_ORDER_LINE)
SO_LINE_NUMBER,
NULL
PROJECT_NUMBER,
NULL
TASK_NUMBER,
REPLACE (
REPLACE (
REPLACE (REPLACE (CL.DESCRIPTION, ';', ','), CHR (10), NULL),
CHR (09),
NULL),
CHR (13),
NULL)
ITEM_DESCRIPTION,
CL.QUANTITY_INVOICED
QTY,
CT.TRX_DATE
INVOICE_DATE,
RC.NAME
TRANSACTION_TYPE,
CT.INVOICE_CURRENCY_CODE
FUNCTIONAL_CURRENCY_FC,
NVL (CSTI.ITEM_COST, 0)
UNIT_COST_FC,
(NVL (CSTI.ITEM_COST, 0) * NVL (CL.QUANTITY_INVOICED, 0))
TOTAL_COST_FC,
DECODE (CT.INVOICE_CURRENCY_CODE,
'BRL', CL.EXTENDED_AMOUNT,
CL.EXTENDED_AMOUNT * CT.EXCHANGE_RATE)
INVOICED_AMOUNT_FC,
HLA.LOCATION_CODE
SHIPPING_ORGANIZATION,
MS.SEGMENT1
ORDERED_ITEM,
GCV_REV.CONCATENATED_SEGMENTS
SALES_ACCOUNT,
GCV_COST.CONCATENATED_SEGMENTS
COST_OF_GOODS_SOLD_ACCOUNT,
CL.GLOBAL_ATTRIBUTE2
FISCAL_CLASSIFICATION_CODE,
NULL
MRO_PROJECT,
HLO.CITY
CUSTOMER_CITY,
CL.GLOBAL_ATTRIBUTE4
ITEM_ORIGIN,
REPLACE (
REPLACE (
REPLACE (REPLACE (MT.DESCRIPTION, ';', ','), CHR (10), NULL),
CHR (09),
NULL),
CHR (13),
NULL)
ITEM_PORTUGUESE_DESCRIPTION,
NULL
PO_RECEIVED_DATE,
CL.GLOBAL_ATTRIBUTE3
TRANSACTION_CONDITION_CLASS,
HLO.STATE
CUSTOMER_STATE,
RC.GLOBAL_ATTRIBUTE3 || '|' || RC.DESCRIPTION
TIPO_DE_FATURAMENTO,
CSTI.COST_TYPE
COST_TYPE,
HCA.ACCOUNT_NUMBER
CUSTOMER_CODE,
CT.CUSTOMER_TRX_ID,
DECODE (HCA.CUSTOMER_TYPE, 'R', 'Externo', 'I', 'Interno')
TYPE_CUSTOMER,
CT.STATUS_TRX,
NVL (ORD_TYPE.NAME, 'Manual Invoice-AR')
SO_ORDER_TYPE,
RC.TYPE
RC_TYPE,
REPLACE (
REPLACE (
REPLACE (REPLACE (CT.ATTRIBUTE1, ';', ':'), CHR (10), NULL),
CHR (13),
NULL),
CHR (09),
NULL)
FINAL_DELIVERY_DATE,
NULL
REQ_DELIVERY_DATE,
NULL
REQ_SHIP_DATE,
NULL
ORIGINAL_PROMISED_DATE,
NULL
PROMISE_DATE,
NULL
SCHEDULE_SHIP_DATE,
NULL
CUSTOMER_PO,
NULL
LINE_TYPE,
NULL
TIER_TYPE,
NULL
TASK_TYPE,
NULL
KOB_HEADER,
NULL
KOB_LINE,
NULL
SHIPPING_TERMS,
NULL
SHIPMENT_PRIORITY,
NULL
ORDER_SOURCE,
NULL
QUOTE_NUMBER,
NULL
PROJECT_TYPE,
NULL
PROJECT_NAME,
(SELECT LPAD (PERIOD_NUM, 2, '0')
FROM APPS.GL_PERIOD_STATUSES
WHERE SET_OF_BOOKS_ID IN (SELECT ATTRIBUTE1
FROM APPS.GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = 443)
AND APPLICATION_ID = 101
AND TRUNC (CT.TRX_DATE) BETWEEN TRUNC (START_DATE)
AND TRUNC (END_DATE))
PERIOD,
CL.WAREHOUSE_ID
SHIP_FROM_ORG_ID,
NULL
OE_LINE_ID,
NULL
OE_HEADER_ID,
CL.INVENTORY_ITEM_ID
INVENTORY_ITEM_ID,
CL.CUSTOMER_TRX_LINE_ID
CUSTOMER_TRX_LINE_ID,
CT.ORG_ID
ORG_ID,
CT.EXCHANGE_RATE
EXCHANGE_RATE,
CL.EXTENDED_AMOUNT
EXTENDED_AMOUNT,
GCV_REV.CODE_COMBINATION_ID
REV_CODE_COMBINATION_ID,
CL.WAREHOUSE_ID
WAREHOUSE_ID,
NULL
END_CUSTOMER_SITE_USE_ID,
(SELECT LPH1.SERVICE_CLOUD_REF
FROM APPS.XXOM_3LP_SYM_ORA_ORDER_HDR LPH1
WHERE LPH1.HEADER_ID = ORD_TYPE.HEADER_ID)
SERVICE_CLOUD_REF
FROM APPS.RA_CUSTOMER_TRX CT,
APPS.RA_CUSTOMER_TRX_LINES CL,
APPS.RA_CUST_TRX_TYPES RC,
APPS.RA_BATCH_SOURCES BS,
APPS.MTL_SYSTEM_ITEMS_B MS,
APPS.MTL_SYSTEM_ITEMS_TL MT,
APPS.CST_ITEM_COST_TYPE_V CSTI,
APPS.HR_LOCATIONS HLA,
APPS.HZ_PARTIES HPA,
APPS.HZ_CUST_ACCOUNTS HCA,
APPS.HZ_PARTY_SITES HPS,
APPS.HZ_LOCATIONS HLO,
APPS.HZ_CUST_ACCT_SITES HCS,
APPS.HZ_CUST_SITE_USES HCU,
APPS.HR_ALL_ORGANIZATION_UNITS HAO,
APPS.HR_ALL_ORGANIZATION_UNITS_TL HAT,
APPS.GL_CODE_COMBINATIONS_KFV GCV_REV,
APPS.RA_CUST_TRX_LINE_GL_DIST RCG,
APPS.GL_CODE_COMBINATIONS_KFV GCV_COST,
APPS.PA_PROJECTS PPA,
APPS.AR_NOTES AN,
(SELECT TO_CHAR (OOHA.ORDER_NUMBER) ORDER_NUMBER,
OTT.NAME,
OOHA.HEADER_ID
FROM APPS.OE_ORDER_HEADERS OOHA, APPS.OE_TRANSACTION_TYPES_TL OTT
WHERE OOHA.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
AND OTT.LANGUAGE = USERENV ('LANG')
AND OOHA.ORG_ID = :B2) ORD_TYPE
WHERE CT.SHIP_TO_SITE_USE_ID = HCU.SITE_USE_ID(+)
AND HCU.CUST_ACCT_SITE_ID = HCS.CUST_ACCT_SITE_ID(+)
AND HCS.PARTY_SITE_ID = HPS.PARTY_SITE_ID(+)
AND HPS.LOCATION_ID = HLO.LOCATION_ID(+)
AND HCS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID(+)
AND HCA.PARTY_ID = HPA.PARTY_ID(+)
AND HCU.SITE_USE_CODE(+) = 'SHIP_TO'
AND CT.CUSTOMER_TRX_ID = CL.CUSTOMER_TRX_ID
AND CL.LINE_TYPE = 'LINE'
AND CT.CUST_TRX_TYPE_ID = RC.CUST_TRX_TYPE_ID
AND RC.ORG_ID = CT.ORG_ID
AND ( (RC.TYPE = 'INV')
OR (NVL (
(SELECT DECODE (FLV.TAG, 'INCLUDE', 'Y', 'N')
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE =
'EMR AR SALES X FUTURE DLVY BR'
AND FLV.LANGUAGE = USERENV ('LANG')
AND FLV.ENABLED_FLAG = 'Y'
AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
TRUNC (SYSDATE)
AND NVL (FLV.END_DATE_ACTIVE,
TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE)
AND FLV.MEANING = RC.NAME),
'N') =
'Y'))
AND NVL (
(SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'EMR AR SALES X FUTURE DLVY BR'
AND FLV.LANGUAGE = USERENV ('LANG')
AND FLV.ENABLED_FLAG = 'Y'
AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
TRUNC (SYSDATE)
AND NVL (FLV.END_DATE_ACTIVE, TRUNC (SYSDATE) + 1) >
TRUNC (SYSDATE)
AND FLV.MEANING = RC.NAME),
'N') =
'N'
AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND BS.ORG_ID = :B2
AND BS.BATCH_SOURCE_TYPE = 'INV'
AND CL.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID(+)
AND CL.WAREHOUSE_ID = MS.ORGANIZATION_ID(+)
AND CL.WAREHOUSE_ID = HLA.INVENTORY_ORGANIZATION_ID(+)
AND HLA.INVENTORY_ORGANIZATION_ID = HAO.ORGANIZATION_ID(+)
AND HAO.ORGANIZATION_ID = HAT.ORGANIZATION_ID(+)
AND CL.SALES_ORDER = PPA.SEGMENT1(+)
AND PPA.ORG_ID(+) = :B2
AND HAT.LANGUAGE(+) = USERENV ('LANG')
AND CT.COMPLETE_FLAG = 'Y'
AND CL.CUSTOMER_TRX_ID = RCG.CUSTOMER_TRX_ID
AND CL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID
AND RCG.CODE_COMBINATION_ID = GCV_REV.CODE_COMBINATION_ID
AND MS.COST_OF_SALES_ACCOUNT = GCV_COST.CODE_COMBINATION_ID
AND RCG.ACCOUNT_CLASS = 'REV'
AND CL.INVENTORY_ITEM_ID = CSTI.INVENTORY_ITEM_ID(+)
AND CL.WAREHOUSE_ID = CSTI.ORGANIZATION_ID(+)
AND CSTI.COST_TYPE(+) = 'Average'
AND MS.INVENTORY_ITEM_ID = MT.INVENTORY_ITEM_ID(+)
AND MS.ORGANIZATION_ID = MT.ORGANIZATION_ID(+)
AND MT.LANGUAGE(+) = USERENV ('LANG')
AND CT.CT_REFERENCE = ORD_TYPE.ORDER_NUMBER(+)
AND CT.CUSTOMER_TRX_ID = AN.CUSTOMER_TRX_ID(+)
AND AN.NOTE_TYPE(+) = 'MAINTAIN'
AND AN.TEXT(+) = 'NOTA EM TRANSITO'
AND NVL (TRUNC (AN.CREATION_DATE), CT.TRX_DATE) BETWEEN :B4 AND :B3
AND NOT ( CT.STATUS_TRX = 'VD'
AND RC.GLOBAL_ATTRIBUTE3 IN
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE =
'EMR_AR_SALESREP_EXC_MAN_INV'
AND DESCRIPTION = 'CFOP'
AND LANGUAGE = USERENV ('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN START_DATE_ACTIVE
AND NVL (END_DATE_ACTIVE,
SYSDATE + 1)
AND TAG = 'HIDE'))
AND UPPER (RC.NAME) IN
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'EMR_AR_SALESREP_EXC_MAN_INV'
AND DESCRIPTION = 'MANUAL INVOICE'
AND LANGUAGE = USERENV ('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN START_DATE_ACTIVE
AND NVL (END_DATE_ACTIVE, SYSDATE + 1)
AND TAG = 'SHOW')
AND CT.ORG_ID = :B2
AND :B5 = 'Y'
ORDER BY 1, 2;
[\code]
Re: Sql is causing performance issue [message #686592 is a reply to message #686590] Tue, 18 October 2022 10:55 Go to previous messageGo to next message
nishant1987
Messages: 8
Registered: September 2022
Junior Member

/* Formatted on 10/18/2022 9:03:08 PM (QP5 v5.318) */ SELECT 
/*+TEST_NMK*/DISTINCT        CT.TRX_NUMBER            INVOICE_NUMBER,   
     CT.DOC_SEQUENCE_VALUE            FISCAL_NUMBER,        
CL.LINE_NUMBER            LINE_NUM,        HPA.PARTY_NAME            
COMPANY_NAME,        HAT.NAME            BUSINESS_UNIT_NAME,        
LPH.SIC_CODE            SIC_CODE,        REPLACE (LPH.SIC_DESCRIPTION, 
';', ',')            SIC_DESCRIPTION,        TO_CHAR 
(OOHA.ORDER_NUMBER)            SO_NUMBER,        JRS.NAME            
SALESPERSON,        TO_CHAR (OOLA.LINE_NUMBER)            
SO_LINE_NUMBER,        PP.SEGMENT1            PROJECT_NUMBER,        
PT.TASK_NUMBER            TASK_NUMBER,        REPLACE (            
REPLACE (                REPLACE (REPLACE (CL.DESCRIPTION, ';', ','), 
CHR (10), NULL),                CHR (09),                NULL),         
   CHR (13),            NULL)            ITEM_DESCRIPTION,        
CL.QUANTITY_INVOICED            QTY,        CT.TRX_DATE            
INVOICE_
 
Plan hash value: 2438366022
 
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                           | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                                |        |       |       |     2 (100)|          |
|   1 |  SORT ORDER BY                                               |                                |      2 |  4234 |       |     1 (100)| 00:00:01 |
|   2 |   UNION-ALL                                                  |                                |        |       |       |            |          |
|*  3 |    FILTER                                                    |                                |        |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID                              | ASO_QUOTE_HEADERS_ALL          |      1 |    12 |       |     3   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN                                       | ASO_QUOTE_HEADERS_ALL_U2       |      1 |       |       |     2   (0)| 00:00:01 |
|   6 |    NESTED LOOPS                                              |                                |      1 |    37 |       |   103   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID                              | GL_LEDGERS                     |      1 |    10 |       |     1   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN                                       | GL_LEDGERS_U2                  |      1 |       |       |     0   (0)|          |
|*  9 |     TABLE ACCESS BY INDEX ROWID                              | GL_PERIOD_STATUSES             |      1 |    27 |       |   102   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN                                        | GL_PERIOD_STATUSES_U2          |    270 |       |       |     3   (0)| 00:00:01 |
|  11 |    TABLE ACCESS BY INDEX ROWID                               | XXOM_3LP_SYM_ORA_ORDER_HDR     |      1 |     8 |       |     4   (0)| 00:00:01 |
|* 12 |     INDEX RANGE SCAN                                         | XXOM_3LP_SYM_ORA_ORDER_HDR_N2  |      1 |       |       |     3   (0)| 00:00:01 |
|  13 |    HASH UNIQUE                                               |                                |      1 |  1257 |       |     1 (100)| 00:00:01 |
|* 14 |     FILTER                                                   |                                |        |       |       |            |          |
|* 15 |      FILTER                                                  |                                |        |       |       |            |          |
|  16 |       NESTED LOOPS OUTER                                     |                                |      1 |  1257 |       |    15M  (1)| 00:10:12 |
|  17 |        NESTED LOOPS OUTER                                    |                                |      1 |  1219 |       |    15M  (1)| 00:10:12 |
|* 18 |         HASH JOIN OUTER                                      |                                |      1 |  1214 |       |    15M  (1)| 00:10:12 |
|* 19 |          HASH JOIN OUTER                                     |                                |      1 |  1194 |       |    15M  (1)| 00:10:12 |
|  20 |           NESTED LOOPS OUTER                                 |                                |      1 |  1180 |       |    15M  (1)| 00:10:01 |
|  21 |            NESTED LOOPS OUTER                                |                                |      1 |  1156 |       |    15M  (1)| 00:10:01 |
|  22 |             NESTED LOOPS OUTER                               |                                |      1 |  1134 |       |    15M  (1)| 00:10:01 |
|  23 |              NESTED LOOPS OUTER                              |                                |      1 |  1091 |       |    15M  (1)| 00:10:01 |
|  24 |               NESTED LOOPS                                   |                                |      1 |  1028 |       |    15M  (1)| 00:10:01 |
|* 25 |                FILTER                                        |                                |        |       |       |            |          |
|* 26 |                 HASH JOIN RIGHT OUTER                        |                                |      1 |   998 |       |    15M  (1)| 00:10:01 |
|* 27 |                  TABLE ACCESS FULL                           | OE_ORDER_SOURCES               |    330 |  7590 |       |     9   (0)| 00:00:01 |
|  28 |                  NESTED LOOPS                                |                                |        |       |       |            |          |
|  29 |                   NESTED LOOPS                               |                                |    898 |   855K|       |    15M  (1)| 00:10:01 |
|  30 |                    NESTED LOOPS OUTER                        |                                |    898 |   807K|       |    15M  (1)| 00:10:01 |
|  31 |                     NESTED LOOPS                             |                                |    898 |   797K|       |    15M  (1)| 00:10:01 |
|* 32 |                      HASH JOIN                               |                                |    577 |   495K|       |    15M  (1)| 00:10:01 |
|* 33 |                       TABLE ACCESS FULL                      | OE_TRANSACTION_TYPES_ALL       |   4124 | 45364 |       |   186   (0)| 00:00:01 |
|  34 |                       NESTED LOOPS                           |                                |    577 |   489K|       |    15M  (1)| 00:10:01 |
|* 35 |                        HASH JOIN OUTER                       |                                |    577 |   440K|       |    15M  (1)| 00:10:01 |
|  36 |                         NESTED LOOPS                         |                                |        |       |       |            |          |
|  37 |                          NESTED LOOPS                        |                                |    577 |   426K|       |  8958K  (1)| 00:05:50 |
|  38 |                           NESTED LOOPS                       |                                |    577 |   398K|       |  8957K  (1)| 00:05:50 |
|* 39 |                            FILTER                            |                                |        |       |       |            |          |
|  40 |                             NESTED LOOPS OUTER               |                                |    577 |   385K|       |  8954K  (1)| 00:05:50 |
|* 41 |                              HASH JOIN RIGHT ANTI SNA        |                                |    577 |   335K|       |  8953K  (1)| 00:05:50 |
|* 42 |                               TABLE ACCESS BY INDEX ROWID    | FND_LOOKUP_VALUES              |      1 |    75 |       |    20   (0)| 00:00:01 |
|* 43 |                                INDEX RANGE SCAN              | XXAR_FND_LOOKUP_VALUES_N1      |     46 |       |       |     4   (0)| 00:00:01 |
|* 44 |                               HASH JOIN                      |                                |    577 |   293K|       |  8953K  (1)| 00:05:50 |
|* 45 |                                TABLE ACCESS BY INDEX ROWID   | RA_CUST_TRX_TYPES_ALL          |      7 |   525 |       |    47   (0)| 00:00:01 |
|* 46 |                                 INDEX SKIP SCAN              | RA_CUST_TRX_TYPES_U1           |     42 |       |       |    37   (0)| 00:00:01 |
|  47 |                                NESTED LOOPS OUTER            |                                |    255K|   108M|       |  8953K  (1)| 00:05:50 |
|* 48 |                                 HASH JOIN                    |                                |    178K|    66M|    60M|  8418K  (1)| 00:05:29 |
|  49 |                                  NESTED LOOPS                |                                |        |       |       |            |          |
|  50 |                                   NESTED LOOPS               |                                |    178K|    58M|       |  8324K  (1)| 00:05:26 |
|* 51 |                                    HASH JOIN                 |                                |    175K|    52M|       |  7796K  (1)| 00:05:05 |
|* 52 | WID                                 TABLE ACCESS BY INDEX RO | RA_BATCH_SOURCES_ALL           |      9 |   225 |       |    32   (0)| 00:00:01 |
|* 53 |                                      INDEX SKIP SCAN         | RA_BATCH_SOURCES_U2            |     17 |       |       |    19   (0)| 00:00:01 |
|* 54 |                                     HASH JOIN                |                                |     14M|  3981M|    56M|  7796K  (1)| 00:05:05 |
|* 55 | OWID                                 TABLE ACCESS BY INDEX R | RA_CUSTOMER_TRX_LINES_ALL      |    654K|    48M|       |  1103K  (1)| 00:00:44 |
|* 56 |                                       INDEX RANGE SCAN       | RTA4                           |     28M|       |       | 89262   (1)| 00:00:04 |
|  57 |                                      NESTED LOOPS            |                                |     14M|  2818M|       |  6367K  (1)| 00:04:09 |
|  58 |                                       NESTED LOOPS OUTER     |                                |    137 | 19043 |       |  3726K  (1)| 00:02:26 |
|* 59 |                                        FILTER                |                                |        |       |       |            |          |
|* 60 | R                                       HASH JOIN RIGHT OUTE |                                |    137 | 14111 |    33M|  3725K  (1)| 00:02:26 |
|  61 |                                          TABLE ACCESS FULL   | HZ_CUST_ACCOUNTS               |    963K|    22M|       | 13952   (1)| 00:00:01 |
|* 62 | ER                                       HASH JOIN RIGHT OUT |                                |    130M|  9835M|    58M|  2490K  (1)| 00:01:38 |
|  63 |                                           TABLE ACCESS FULL  | HZ_LOCATIONS                   |   1961K|    35M|       | 35675   (1)| 00:00:02 |
|* 64 | TER                                       HASH JOIN RIGHT OU |                                |    130M|  7470M|    60M|  1484K  (1)| 00:00:58 |
|  65 |                                            TABLE ACCESS FULL | HZ_PARTY_SITES                 |   2123K|    36M|       | 24300   (1)| 00:00:01 |
|* 66 | UTER                                       HASH JOIN RIGHT O |                                |    130M|  5229M|       |   729K  (1)| 00:00:29 |
|  67 |                                             VIEW             | HZ_CUST_ACCT_SITES_ALL#        |      1 |    19 |       |     5 (100)| 00:00:01 |
|* 68 |                                              FILTER          |                                |        |       |       |            |          |
|  69 | ULL                                           TABLE ACCESS F | HZ_CUST_ACCT_SITES_ALL         |    108M|  1969M|       |  1295K  (1)| 00:00:51 |
|  70 | L                                           TABLE ACCESS FUL | HZ_CUST_SITE_USES_ALL          |    130M|  2863M|       |   729K  (1)| 00:00:29 |
|  71 |  ROWID                                 TABLE ACCESS BY INDEX | HZ_PARTIES                     |      1 |    36 |       |     2   (0)| 00:00:01 |
|* 72 |                                         INDEX UNIQUE SCAN    | HZ_PARTIES_U1                  |      1 |       |       |     1   (0)| 00:00:01 |
|  73 | ROWID                                 TABLE ACCESS BY INDEX  | RA_CUSTOMER_TRX_ALL            |    103K|  6977K|       | 19278   (1)| 00:00:01 |
|* 74 |                                        INDEX SKIP SCAN       | RA_CUSTOMER_TRX_X7             |    103K|       |       |   168   (1)| 00:00:01 |
|* 75 |                                    INDEX UNIQUE SCAN         | MTL_SYSTEM_ITEMS_B_U1          |      1 |       |       |     2   (0)| 00:00:01 |
|  76 | D                                 TABLE ACCESS BY INDEX ROWI | MTL_SYSTEM_ITEMS_B             |      1 |    32 |       |     3   (0)| 00:00:01 |
|  77 |                                  TABLE ACCESS FULL           | GL_CODE_COMBINATIONS           |   6126K|   286M|       | 49664   (1)| 00:00:02 |
|  78 |                                 TABLE ACCESS BY INDEX ROWID  | MTL_SYSTEM_ITEMS_TL            |      1 |    54 |       |     3   (0)| 00:00:01 |
|* 79 |                                  INDEX UNIQUE SCAN           | MTL_SYSTEM_ITEMS_TL_U1         |      1 |       |       |     2   (0)| 00:00:01 |
|* 80 |                              TABLE ACCESS BY INDEX ROWID     | AR_NOTES                       |      1 |    89 |       |     4   (0)| 00:00:01 |
|* 81 |                               INDEX RANGE SCAN               | AR_NOTES_N3                    |      2 |       |       |     2   (0)| 00:00:01 |
|* 82 |                            TABLE ACCESS BY INDEX ROWID       | RA_CUST_TRX_LINE_GL_DIST_ALL   |      1 |    23 |       |     4   (0)| 00:00:01 |
|* 83 |                             INDEX RANGE SCAN                 | RA_CUST_TRX_LINE_GL_DIST_N1    |      1 |       |       |     3   (0)| 00:00:01 |
|* 84 |                           INDEX UNIQUE SCAN                  | GL_CODE_COMBINATIONS_U1        |      1 |       |       |     1   (0)| 00:00:01 |
|  85 |                          TABLE ACCESS BY INDEX ROWID         | GL_CODE_COMBINATIONS           |      1 |    49 |       |     2   (0)| 00:00:01 |
|  86 |                         VIEW                                 | CST_ITEM_COST_TYPE_V           |    150K|  3683K|       |  6403K  (1)| 00:04:11 |
|* 87 |                          HASH JOIN RIGHT OUTER               |                                |    150K|    24M|       |  6403K  (1)| 00:04:11 |
|* 88 |                           INDEX RANGE SCAN                   | FND_LOOKUP_VALUES_U1           |      1 |    48 |       |     4   (0)| 00:00:01 |
|  89 |                           NESTED LOOPS                       |                                |    150K|    17M|       |  6403K  (1)| 00:04:11 |
|  90 |                            NESTED LOOPS                      |                                |  96233 |    10M|       |  6210K  (1)| 00:04:03 |
|* 91 |                             FILTER                           |                                |        |       |       |            |          |
|  92 |                              NESTED LOOPS OUTER              |                                |    189K|    16M|       |  5659K  (1)| 00:03:42 |
|* 93 |                               HASH JOIN                      |                                |   1519K|   110M|       |  1099K  (2)| 00:00:43 |
|  94 |                                NESTED LOOPS                  |                                |      1 |    57 |       |     3   (0)| 00:00:01 |
|  95 |                                 NESTED LOOPS                 |                                |      1 |    44 |       |     3   (0)| 00:00:01 |
|  96 |                                  TABLE ACCESS BY INDEX ROWID | MTL_DEFAULT_CATEGORY_SETS      |      1 |    17 |       |     1   (0)| 00:00:01 |
|* 97 |                                   INDEX UNIQUE SCAN          | MTL_DEFAULT_CATEGORY_SETS_U1   |      1 |       |       |     0   (0)|          |
|  98 |                                  TABLE ACCESS BY INDEX ROWID | CST_COST_TYPES                 |      1 |    27 |       |     2   (0)| 00:00:01 |
|* 99 |                                   INDEX RANGE SCAN           | CST_COST_TYPES_U2              |      1 |       |       |     1   (0)| 00:00:01 |
|*100 |                                 INDEX UNIQUE SCAN            | CST_COST_TYPES_U1              |      1 |    13 |       |     0   (0)|          |
| 101 |                                TABLE ACCESS FULL             | CST_ITEM_COSTS                 |    336M|  6095M|       |  1097K  (2)| 00:00:43 |
|*102 |                               INDEX RANGE SCAN               | MTL_ITEM_CATEGORIES_U1         |      1 |    17 |       |     3   (0)| 00:00:01 |
| 103 |                             TABLE ACCESS BY INDEX ROWID      | MTL_SYSTEM_ITEMS_B             |      1 |    16 |       |     3   (0)| 00:00:01 |
|*104 |                              INDEX UNIQUE SCAN               | MTL_SYSTEM_ITEMS_B_UX2         |      1 |       |       |     2   (0)| 00:00:01 |
|*105 |                            INDEX UNIQUE SCAN                 | MTL_SYSTEM_ITEMS_TL_U1         |      2 |    28 |       |     2   (0)| 00:00:01 |
| 106 |                        TABLE ACCESS BY INDEX ROWID           | OE_ORDER_LINES_ALL             |      1 |    86 |       |     4   (0)| 00:00:01 |
|*107 |                         INDEX RANGE SCAN                     | OE_ORDER_LINES_X3              |      1 |       |       |     3   (0)| 00:00:01 |
| 108 |                      TABLE ACCESS BY INDEX ROWID             | OE_TRANSACTION_TYPES_TL        |      2 |    60 |       |     1   (0)| 00:00:01 |
|*109 |                       INDEX UNIQUE SCAN                      | OE_TRANSACTION_TYPES_TL_U1     |      1 |       |       |     0   (0)|          |
| 110 |                     TABLE ACCESS BY INDEX ROWID              | PA_TASKS                       |      1 |    12 |       |     2   (0)| 00:00:01 |
|*111 |                      INDEX UNIQUE SCAN                       | PA_TASKS_U1                    |      1 |       |       |     1   (0)| 00:00:01 |
|*112 |                    INDEX UNIQUE SCAN                         | OE_ORDER_HEADERS_U1            |      1 |       |       |     1   (0)| 00:00:01 |
| 113 |                   TABLE ACCESS BY INDEX ROWID                | OE_ORDER_HEADERS_ALL           |      1 |    54 |       |     2   (0)| 00:00:01 |
| 114 |                TABLE ACCESS BY INDEX ROWID                   | OE_TRANSACTION_TYPES_TL        |      2 |    60 |       |     1   (0)| 00:00:01 |
|*115 |                 INDEX UNIQUE SCAN                            | OE_TRANSACTION_TYPES_TL_U1     |      1 |       |       |     0   (0)|          |
|*116 |               TABLE ACCESS BY INDEX ROWID                    | FND_LOOKUP_VALUES              |      1 |    63 |       |     4   (0)| 00:00:01 |
|*117 |                INDEX RANGE SCAN                              | FND_LOOKUP_VALUES_X99          |      1 |       |       |     3   (0)| 00:00:01 |
| 118 |              TABLE ACCESS BY INDEX ROWID                     | XXOM_3LP_SYM_ORA_ORDER_HDR     |      1 |    43 |       |     3   (0)| 00:00:01 |
|*119 |               INDEX RANGE SCAN                               | XXOM_3LP_SYM_ORA_ORDER_HDR_N2  |      1 |       |       |     2   (0)| 00:00:01 |
| 120 |             TABLE ACCESS BY INDEX ROWID                      | XXOM_3LP_SYM_ORA_ORDER_LINES   |      1 |    22 |       |     4   (0)| 00:00:01 |
|*121 |              INDEX RANGE SCAN                                | XXOM_3LP_SYM_ORA_ORDER_LIN_N6  |      1 |       |       |     3   (0)| 00:00:01 |
| 122 |            TABLE ACCESS BY INDEX ROWID                       | JTF_RS_SALESREPS               |      1 |    24 |       |     2   (0)| 00:00:01 |
|*123 |             INDEX RANGE SCAN                                 | JTF_RS_SALESREPS_U1            |      1 |       |       |     1   (0)| 00:00:01 |
| 124 |           VIEW                                               |                                |     18M|   247M|       |   295K  (1)| 00:00:12 |
|*125 |            HASH JOIN                                         |                                |     18M|   564M|       |   295K  (1)| 00:00:12 |
|*126 |             TABLE ACCESS FULL                                | PA_TASK_TYPES                  |     70 |  1540 |       |     5   (0)| 00:00:01 |
| 127 |             TABLE ACCESS FULL                                | PA_PROJ_ELEMENTS               |     19M|   182M|       |   295K  (1)| 00:00:12 |
| 128 |          VIEW                                                | HR_LOCATIONS                   |     60 |  1200 |       |   163   (2)| 00:00:01 |
| 129 |           NESTED LOOPS                                       |                                |        |       |       |            |          |
| 130 |            NESTED LOOPS                                      |                                |     60 |  2280 |       |   163   (2)| 00:00:01 |
|*131 |             TABLE ACCESS FULL                                | HR_LOCATIONS_ALL               |     38 |   418 |       |   125   (2)| 00:00:01 |
|*132 |             INDEX UNIQUE SCAN                                | HR_LOCATIONS_ALL_TL_PK         |      1 |       |       |     0   (0)|          |
| 133 |            TABLE ACCESS BY INDEX ROWID                       | HR_LOCATIONS_ALL_TL            |      2 |    54 |       |     1   (0)| 00:00:01 |
|*134 |         INDEX UNIQUE SCAN                                    | HR_ORGANIZATION_UNITS_PK       |      1 |     5 |       |     0   (0)|          |
| 135 |        TABLE ACCESS BY INDEX ROWID                           | HR_ALL_ORGANIZATION_UNITS_TL   |      1 |    38 |       |     1   (0)| 00:00:01 |
|*136 |         INDEX UNIQUE SCAN                                    | HR_ALL_ORGANIZATION_UNTS_TL_PK |      1 |       |       |     0   (0)|          |
|*137 |      TABLE ACCESS BY INDEX ROWID                             | FND_LOOKUP_VALUES              |      1 |    64 |       |    17   (0)| 00:00:01 |
|*138 |       INDEX RANGE SCAN                                       | FND_LOOKUP_VALUES_U2           |      1 |       |       |    16   (0)| 00:00:01 |
|*139 |      TABLE ACCESS BY INDEX ROWID                             | FND_LOOKUP_VALUES              |      1 |    64 |       |    17   (0)| 00:00:01 |
|*140 |       INDEX RANGE SCAN                                       | FND_LOOKUP_VALUES_U2           |      1 |       |       |    16   (0)| 00:00:01 |
|*141 |      TABLE ACCESS BY INDEX ROWID                             | FND_LOOKUP_VALUES              |      1 |    75 |       |    16   (0)| 00:00:01 |
|*142 |       INDEX RANGE SCAN                                       | FND_LOOKUP_VALUES_U1           |      1 |       |       |    14   (0)| 00:00:01 |
| 143 |    NESTED LOOPS                                              |                                |      1 |    37 |       |   103   (0)| 00:00:01 |
|*144 |     TABLE ACCESS BY INDEX ROWID                              | GL_LEDGERS                     |      1 |    10 |       |     1   (0)| 00:00:01 |
|*145 |      INDEX UNIQUE SCAN                                       | GL_LEDGERS_U2                  |      1 |       |       |     0   (0)|          |
|*146 |     TABLE ACCESS BY INDEX ROWID                              | GL_PERIOD_STATUSES             |      1 |    27 |       |   102   (0)| 00:00:01 |
|*147 |      INDEX RANGE SCAN                                        | GL_PERIOD_STATUSES_U2          |    270 |       |       |     3   (0)| 00:00:01 |
|*148 |    FILTER                                                    |                                |        |       |       |            |          |
| 149 |     TABLE ACCESS FULL                                        | XXOM_3LP_SYM_ORA_ORDER_HDR     |   6110K|    46M|       |   175K  (1)| 00:00:07 |
|*150 |    FILTER                                                    |                                |        |       |       |            |          |
|*151 |     FILTER                                                   |                                |        |       |       |            |          |
| 152 |      NESTED LOOPS                                            |                                |        |       |       |            |          |
| 153 |       NESTED LOOPS                                           |                                |      1 |   828 |       |  6404K  (1)| 00:04:11 |
| 154 |        NESTED LOOPS                                          |                                |      1 |   779 |       |  6404K  (1)| 00:04:11 |
| 155 |         NESTED LOOPS OUTER                                   |                                |      1 |   730 |       |  6404K  (1)| 00:04:11 |
| 156 |          NESTED LOOPS                                        |                                |      1 |   676 |       |  6404K  (1)| 00:04:11 |
| 157 |           NESTED LOOPS                                       |                                |      1 |   644 |       |  6404K  (1)| 00:04:11 |
| 158 |            NESTED LOOPS OUTER                                |                                |      1 |   621 |       |  6404K  (1)| 00:04:11 |
|*159 |             HASH JOIN OUTER                                  |                                |      1 |   585 |       |  6404K  (1)| 00:04:11 |
| 160 |              NESTED LOOPS OUTER                              |                                |      1 |   554 |       |   861   (1)| 00:00:01 |
| 161 |               NESTED LOOPS OUTER                             |                                |      1 |   516 |       |   860   (1)| 00:00:01 |
|*162 |                HASH JOIN OUTER                               |                                |      1 |   511 |       |   860   (1)| 00:00:01 |
| 163 |                 NESTED LOOPS                                 |                                |        |       |       |            |          |
| 164 |                  NESTED LOOPS                                |                                |      1 |   476 |       |   698   (1)| 00:00:01 |
| 165 |                   NESTED LOOPS OUTER                         |                                |      1 |   413 |       |   691   (1)| 00:00:01 |
| 166 |                    NESTED LOOPS OUTER                        |                                |      1 |   394 |       |   689   (1)| 00:00:01 |
| 167 |                     NESTED LOOPS OUTER                       |                                |      1 |   382 |       |   687   (1)| 00:00:01 |
| 168 |                      NESTED LOOPS                            |                                |      1 |   358 |       |   685   (1)| 00:00:01 |
|*169 |                       HASH JOIN OUTER                        |                                |      1 |   333 |       |   684   (1)| 00:00:01 |
|*170 |                        HASH JOIN OUTER                       |                                |      1 |   314 |       |   683   (1)| 00:00:01 |
|*171 |                         FILTER                               |                                |        |       |       |            |          |
| 172 |                          NESTED LOOPS OUTER                  |                                |      1 |   300 |       |   683   (1)| 00:00:01 |
| 173 |                           NESTED LOOPS                       |                                |      1 |   211 |       |   679   (1)| 00:00:01 |
| 174 |                            NESTED LOOPS                      |                                |      1 |   148 |       |    68   (2)| 00:00:01 |
| 175 |                             SORT UNIQUE                      |                                |      1 |    75 |       |    20   (0)| 00:00:01 |
|*176 |                              TABLE ACCESS BY INDEX ROWID     | FND_LOOKUP_VALUES              |      1 |    75 |       |    20   (0)| 00:00:01 |
|*177 |                               INDEX RANGE SCAN               | XXAR_FND_LOOKUP_VALUES_N1      |     46 |       |       |     4   (0)| 00:00:01 |
|*178 |                             TABLE ACCESS BY INDEX ROWID      | RA_CUST_TRX_TYPES_ALL          |      1 |    73 |       |    47   (0)| 00:00:01 |
|*179 |                              INDEX SKIP SCAN                 | RA_CUST_TRX_TYPES_U1           |     42 |       |       |    37   (0)| 00:00:01 |
|*180 |                            TABLE ACCESS BY INDEX ROWID       | RA_CUSTOMER_TRX_ALL            |     33 |  2079 |       |   611   (1)| 00:00:01 |
|*181 |                             INDEX RANGE SCAN                 | RA_CUSTOMER_TRX_X4             |     33 |       |       |   595   (1)| 00:00:01 |
|*182 |                           TABLE ACCESS BY INDEX ROWID        | AR_NOTES                       |      1 |    89 |       |     4   (0)| 00:00:01 |
|*183 |                            INDEX RANGE SCAN                  | AR_NOTES_N3                    |      2 |       |       |     2   (0)| 00:00:01 |
| 184 |                         VIEW                                 | HZ_CUST_SITE_USES_ALL#         |      1 |    14 |       |     5 (100)| 00:00:01 |
|*185 |                          FILTER                              |                                |        |       |       |            |          |
| 186 |                           TABLE ACCESS BY INDEX ROWID        | HZ_CUST_SITE_USES_ALL          |   5934K|   130M|       |   327K  (1)| 00:00:13 |
|*187 |                            INDEX RANGE SCAN                  | HZ_CUST_SITE_USES_ALL_N99      |   5934K|       |       | 27110   (1)| 00:00:02 |
| 188 |                        VIEW                                  | HZ_CUST_ACCT_SITES_ALL#        |      1 |    19 |       |     5 (100)| 00:00:01 |
|*189 |                         FILTER                               |                                |        |       |       |            |          |
| 190 |                          TABLE ACCESS FULL                   | HZ_CUST_ACCT_SITES_ALL         |    108M|  1969M|       |  1295K  (1)| 00:00:51 |
|*191 |                       TABLE ACCESS BY INDEX ROWID            | RA_BATCH_SOURCES_ALL           |      1 |    25 |       |     1   (0)| 00:00:01 |
|*192 |                        INDEX UNIQUE SCAN                     | RA_BATCH_SOURCES_U2            |      1 |       |       |     0   (0)|          |
| 193 |                      TABLE ACCESS BY INDEX ROWID             | HZ_CUST_ACCOUNTS               |      1 |    24 |       |     2   (0)| 00:00:01 |
|*194 |                       INDEX UNIQUE SCAN                      | HZ_CUST_ACCOUNTS_U1            |      1 |       |       |     1   (0)| 00:00:01 |
| 195 |                     TABLE ACCESS BY INDEX ROWID              | HZ_PARTY_SITES                 |      1 |    12 |       |     2   (0)| 00:00:01 |
|*196 |                      INDEX UNIQUE SCAN                       | HZ_PARTY_SITES_U1              |      1 |       |       |     1   (0)| 00:00:01 |
| 197 |                    TABLE ACCESS BY INDEX ROWID               | HZ_LOCATIONS                   |      1 |    19 |       |     2   (0)| 00:00:01 |
|*198 |                     INDEX UNIQUE SCAN                        | HZ_LOCATIONS_U1                |      1 |       |       |     1   (0)| 00:00:01 |
|*199 |                   INDEX RANGE SCAN                           | RA_CUSTOMER_TRX_LINES_N14      |     10 |       |       |     3   (0)| 00:00:01 |
|*200 |                  TABLE ACCESS BY INDEX ROWID                 | RA_CUSTOMER_TRX_LINES_ALL      |      1 |    63 |       |     7   (0)| 00:00:01 |
| 201 |                 VIEW                                         | HR_LOCATIONS                   |     60 |  2100 |       |   163   (2)| 00:00:01 |
| 202 |                  NESTED LOOPS                                |                                |        |       |       |            |          |
| 203 |                   NESTED LOOPS                               |                                |     60 |  2280 |       |   163   (2)| 00:00:01 |
|*204 |                    TABLE ACCESS FULL                         | HR_LOCATIONS_ALL               |     38 |   418 |       |   125   (2)| 00:00:01 |
|*205 |                    INDEX UNIQUE SCAN                         | HR_LOCATIONS_ALL_TL_PK         |      1 |       |       |     0   (0)|          |
| 206 |                   TABLE ACCESS BY INDEX ROWID                | HR_LOCATIONS_ALL_TL            |      2 |    54 |       |     1   (0)| 00:00:01 |
|*207 |                INDEX UNIQUE SCAN                             | HR_ORGANIZATION_UNITS_PK       |      1 |     5 |       |     0   (0)|          |
| 208 |               TABLE ACCESS BY INDEX ROWID                    | HR_ALL_ORGANIZATION_UNITS_TL   |      1 |    38 |       |     1   (0)| 00:00:01 |
|*209 |                INDEX UNIQUE SCAN                             | HR_ALL_ORGANIZATION_UNTS_TL_PK |      1 |       |       |     0   (0)|          |
| 210 |              VIEW                                            | CST_ITEM_COST_TYPE_V           |    150K|  4567K|       |  6403K  (1)| 00:04:11 |
|*211 |               HASH JOIN RIGHT OUTER                          |                                |    150K|    24M|       |  6403K  (1)| 00:04:11 |
|*212 |                INDEX RANGE SCAN                              | FND_LOOKUP_VALUES_U1           |      1 |    48 |       |     4   (0)| 00:00:01 |
| 213 |                NESTED LOOPS                                  |                                |    150K|    17M|       |  6403K  (1)| 00:04:11 |
| 214 |                 NESTED LOOPS                                 |                                |  96233 |    10M|       |  6210K  (1)| 00:04:03 |
|*215 |                  FILTER                                      |                                |        |       |       |            |          |
| 216 |                   NESTED LOOPS OUTER                         |                                |    189K|    16M|       |  5659K  (1)| 00:03:42 |
|*217 |                    HASH JOIN                                 |                                |   1519K|   110M|       |  1099K  (2)| 00:00:43 |
| 218 |                     NESTED LOOPS                             |                                |      1 |    57 |       |     3   (0)| 00:00:01 |
| 219 |                      NESTED LOOPS                            |                                |      1 |    44 |       |     3   (0)| 00:00:01 |
| 220 |                       TABLE ACCESS BY INDEX ROWID            | MTL_DEFAULT_CATEGORY_SETS      |      1 |    17 |       |     1   (0)| 00:00:01 |
|*221 |                        INDEX UNIQUE SCAN                     | MTL_DEFAULT_CATEGORY_SETS_U1   |      1 |       |       |     0   (0)|          |
| 222 |                       TABLE ACCESS BY INDEX ROWID            | CST_COST_TYPES                 |      1 |    27 |       |     2   (0)| 00:00:01 |
|*223 |                        INDEX RANGE SCAN                      | CST_COST_TYPES_U2              |      1 |       |       |     1   (0)| 00:00:01 |
|*224 |                      INDEX UNIQUE SCAN                       | CST_COST_TYPES_U1              |      1 |    13 |       |     0   (0)|          |
| 225 |                     TABLE ACCESS FULL                        | CST_ITEM_COSTS                 |    336M|  6095M|       |  1097K  (2)| 00:00:43 |
|*226 |                    INDEX RANGE SCAN                          | MTL_ITEM_CATEGORIES_U1         |      1 |    17 |       |     3   (0)| 00:00:01 |
| 227 |                  TABLE ACCESS BY INDEX ROWID                 | MTL_SYSTEM_ITEMS_B             |      1 |    16 |       |     3   (0)| 00:00:01 |
|*228 |                   INDEX UNIQUE SCAN                          | MTL_SYSTEM_ITEMS_B_UX2         |      1 |       |       |     2   (0)| 00:00:01 |
|*229 |                 INDEX UNIQUE SCAN                            | MTL_SYSTEM_ITEMS_TL_U1         |      2 |    28 |       |     2   (0)| 00:00:01 |
| 230 |             TABLE ACCESS BY INDEX ROWID                      | HZ_PARTIES                     |      1 |    36 |       |     2   (0)| 00:00:01 |
|*231 |              INDEX UNIQUE SCAN                               | HZ_PARTIES_U1                  |      1 |       |       |     1   (0)| 00:00:01 |
|*232 |            TABLE ACCESS BY INDEX ROWID                       | RA_CUST_TRX_LINE_GL_DIST_ALL   |      1 |    23 |       |     4   (0)| 00:00:01 |
|*233 |             INDEX RANGE SCAN                                 | RA_CUST_TRX_LINE_GL_DIST_N1    |      1 |       |       |     3   (0)| 00:00:01 |
| 234 |           TABLE ACCESS BY INDEX ROWID                        | MTL_SYSTEM_ITEMS_B             |      1 |    32 |       |     3   (0)| 00:00:01 |
|*235 |            INDEX UNIQUE SCAN                                 | MTL_SYSTEM_ITEMS_B_U1          |      1 |       |       |     2   (0)| 00:00:01 |
| 236 |          TABLE ACCESS BY INDEX ROWID                         | MTL_SYSTEM_ITEMS_TL            |      1 |    54 |       |     3   (0)| 00:00:01 |
|*237 |           INDEX UNIQUE SCAN                                  | MTL_SYSTEM_ITEMS_TL_U1         |      1 |       |       |     2   (0)| 00:00:01 |
| 238 |         TABLE ACCESS BY INDEX ROWID                          | GL_CODE_COMBINATIONS           |      1 |    49 |       |     2   (0)| 00:00:01 |
|*239 |          INDEX UNIQUE SCAN                                   | GL_CODE_COMBINATIONS_U1        |      1 |       |       |     1   (0)| 00:00:01 |
|*240 |        INDEX UNIQUE SCAN                                     | GL_CODE_COMBINATIONS_U1        |      1 |       |       |     1   (0)| 00:00:01 |
| 241 |       TABLE ACCESS BY INDEX ROWID                            | GL_CODE_COMBINATIONS           |      1 |    49 |       |     2   (0)| 00:00:01 |
|*242 |     TABLE ACCESS BY INDEX ROWID                              | FND_LOOKUP_VALUES              |      1 |    75 |       |    20   (0)| 00:00:01 |
|*243 |      INDEX RANGE SCAN                                        | XXAR_FND_LOOKUP_VALUES_N1      |     46 |       |       |     4   (0)| 00:00:01 |
|*244 |     TABLE ACCESS BY INDEX ROWID                              | FND_LOOKUP_VALUES              |      1 |    64 |       |    17   (0)| 00:00:01 |
|*245 |      INDEX RANGE SCAN                                        | FND_LOOKUP_VALUES_U2           |      1 |       |       |    16   (0)| 00:00:01 |
|*246 |     TABLE ACCESS BY INDEX ROWID                              | FND_LOOKUP_VALUES              |      1 |    64 |       |    17   (0)| 00:00:01 |
|*247 |      INDEX RANGE SCAN                                        | FND_LOOKUP_VALUES_U2           |      1 |       |       |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SET$1       
   3 - SEL$641071AC
   4 - SEL$641071AC / ASO_QUOTE_HEADERS_ALL@SEL$4
   5 - SEL$641071AC / ASO_QUOTE_HEADERS_ALL@SEL$4
   6 - SEL$FCA2BD42
   7 - SEL$FCA2BD42 / LGR@SEL$7
   8 - SEL$FCA2BD42 / LGR@SEL$7
   9 - SEL$FCA2BD42 / GL_PERIOD_STATUSES@SEL$5
  10 - SEL$FCA2BD42 / GL_PERIOD_STATUSES@SEL$5
  11 - SEL$8        / LPH1@SEL$8
  12 - SEL$8        / LPH1@SEL$8
  13 - SEL$7D13FD94
  27 - SEL$7D13FD94 / OE_ORDER_SOURCES@SEL$49
  33 - SEL$7D13FD94 / OE_TRANSACTION_TYPES_ALL@SEL$52
  42 - SEL$7D13FD94 / FND_LOOKUP_VALUES@SEL$63
  43 - SEL$7D13FD94 / FND_LOOKUP_VALUES@SEL$63
  45 - SEL$7D13FD94 / RA_CUST_TRX_TYPES_ALL@SEL$15
  46 - SEL$7D13FD94 / RA_CUST_TRX_TYPES_ALL@SEL$15
  52 - SEL$7D13FD94 / RA_BATCH_SOURCES_ALL@SEL$18
  53 - SEL$7D13FD94 / RA_BATCH_SOURCES_ALL@SEL$18
  55 - SEL$7D13FD94 / RA_CUSTOMER_TRX_LINES_ALL@SEL$12
  56 - SEL$7D13FD94 / RA_CUSTOMER_TRX_LINES_ALL@SEL$12
  61 - SEL$7D13FD94 / HCA@SEL$1
  63 - SEL$7D13FD94 / HLO@SEL$1
  65 - SEL$7D13FD94 / HPS@SEL$1
  67 - SEL$9CF1E98E / HCS@SEL$1
  68 - SEL$9CF1E98E
  69 - SEL$9CF1E98E / HZ_CUST_ACCT_SITES_ALL@SEL$33
  70 - SEL$7D13FD94 / HZ_CUST_SITE_USES_ALL@SEL$35
  71 - SEL$7D13FD94 / HPA@SEL$1
  72 - SEL$7D13FD94 / HPA@SEL$1
  73 - SEL$7D13FD94 / RA_CUSTOMER_TRX_ALL@SEL$10
  74 - SEL$7D13FD94 / RA_CUSTOMER_TRX_ALL@SEL$10
  75 - SEL$7D13FD94 / MS@SEL$1
  76 - SEL$7D13FD94 / MS@SEL$1
  77 - SEL$7D13FD94 / GL_CODE_COMBINATIONS@SEL$41
  78 - SEL$7D13FD94 / MT@SEL$1
  79 - SEL$7D13FD94 / MT@SEL$1
  80 - SEL$7D13FD94 / AN@SEL$1
  81 - SEL$7D13FD94 / AN@SEL$1
  82 - SEL$7D13FD94 / RA_CUST_TRX_LINE_GL_DIST_ALL@SEL$40
  83 - SEL$7D13FD94 / RA_CUST_TRX_LINE_GL_DIST_ALL@SEL$40
  84 - SEL$7D13FD94 / GL_CODE_COMBINATIONS@SEL$38
  85 - SEL$7D13FD94 / GL_CODE_COMBINATIONS@SEL$38
  86 - SEL$310B268A / CSTI@SEL$1
  87 - SEL$310B268A
  88 - SEL$310B268A / FND_LOOKUP_VALUES@SEL$30
  96 - SEL$310B268A / MTL_DEFAULT_CATEGORY_SETS@SEL$27
  97 - SEL$310B268A / MTL_DEFAULT_CATEGORY_SETS@SEL$27
  98 - SEL$310B268A / CST_COST_TYPES@SEL$21
  99 - SEL$310B268A / CST_COST_TYPES@SEL$21
 100 - SEL$310B268A / CST_COST_TYPES@SEL$23
 101 - SEL$310B268A / CIC@SEL$19
 102 - SEL$310B268A / MIC@SEL$19
 103 - SEL$310B268A / MTL_SYSTEM_ITEMS_B@SEL$25
 104 - SEL$310B268A / MTL_SYSTEM_ITEMS_B@SEL$25
 105 - SEL$310B268A / T@SEL$24
 106 - SEL$7D13FD94 / OE_ORDER_LINES_ALL@SEL$43
 107 - SEL$7D13FD94 / OE_ORDER_LINES_ALL@SEL$43
 108 - SEL$7D13FD94 / T@SEL$50
 109 - SEL$7D13FD94 / T@SEL$50
 110 - SEL$7D13FD94 / PT@SEL$1
 111 - SEL$7D13FD94 / PT@SEL$1
 112 - SEL$7D13FD94 / OE_ORDER_HEADERS_ALL@SEL$45
 113 - SEL$7D13FD94 / OE_ORDER_HEADERS_ALL@SEL$45
 114 - SEL$7D13FD94 / OTTT@SEL$1
 115 - SEL$7D13FD94 / OTTT@SEL$1
 116 - SEL$7D13FD94 / FND_LOOKUP_VALUES@SEL$54
 117 - SEL$7D13FD94 / FND_LOOKUP_VALUES@SEL$54
 118 - SEL$7D13FD94 / LPH@SEL$1
 119 - SEL$7D13FD94 / LPH@SEL$1
 120 - SEL$7D13FD94 / LPL@SEL$1
 121 - SEL$7D13FD94 / LPL@SEL$1
 122 - SEL$7D13FD94 / JRS@SEL$1
 123 - SEL$7D13FD94 / JRS@SEL$1
 124 - SEL$141D79C4 / TT@SEL$1
 125 - SEL$141D79C4
 126 - SEL$141D79C4 / PA_TASK_TYPES@SEL$57
 127 - SEL$141D79C4 / PPE@SEL$55
 128 - SEL$31       / HLA@SEL$1
 129 - SEL$31      
 131 - SEL$31       / LOC@SEL$31
 132 - SEL$31       / LOT@SEL$31
 133 - SEL$31       / LOT@SEL$31
 134 - SEL$7D13FD94 / HAO@SEL$1
 135 - SEL$7D13FD94 / HR_ALL_ORGANIZATION_UNITS_TL@SEL$37
 136 - SEL$7D13FD94 / HR_ALL_ORGANIZATION_UNITS_TL@SEL$37
 137 - SEL$3AD37D7B / FND_LOOKUP_VALUES@SEL$60
 138 - SEL$3AD37D7B / FND_LOOKUP_VALUES@SEL$60
 139 - SEL$BB23D85A / FND_LOOKUP_VALUES@SEL$66
 140 - SEL$BB23D85A / FND_LOOKUP_VALUES@SEL$66
 141 - SEL$13566A37 / FND_LOOKUP_VALUES@SEL$69
 142 - SEL$13566A37 / FND_LOOKUP_VALUES@SEL$69
 143 - SEL$532AF31F
 144 - SEL$532AF31F / LGR@SEL$73
 145 - SEL$532AF31F / LGR@SEL$73
 146 - SEL$532AF31F / GL_PERIOD_STATUSES@SEL$71
 147 - SEL$532AF31F / GL_PERIOD_STATUSES@SEL$71
 148 - SEL$74      
 149 - SEL$74       / LPH1@SEL$74
 150 - SEL$2E10BFB3
 176 - SEL$2E10BFB3 / FND_LOOKUP_VALUES@SEL$124
 177 - SEL$2E10BFB3 / FND_LOOKUP_VALUES@SEL$124
 178 - SEL$2E10BFB3 / RA_CUST_TRX_TYPES_ALL@SEL$81
 179 - SEL$2E10BFB3 / RA_CUST_TRX_TYPES_ALL@SEL$81
 180 - SEL$2E10BFB3 / RA_CUSTOMER_TRX_ALL@SEL$76
 181 - SEL$2E10BFB3 / RA_CUSTOMER_TRX_ALL@SEL$76
 182 - SEL$2E10BFB3 / AN@SEL$70
 183 - SEL$2E10BFB3 / AN@SEL$70
 184 - SEL$E063BFF7 / HCU@SEL$70
 185 - SEL$E063BFF7
 186 - SEL$E063BFF7 / HZ_CUST_SITE_USES_ALL@SEL$101
 187 - SEL$E063BFF7 / HZ_CUST_SITE_USES_ALL@SEL$101
 188 - SEL$D790C53A / HCS@SEL$70
 189 - SEL$D790C53A
 190 - SEL$D790C53A / HZ_CUST_ACCT_SITES_ALL@SEL$99
 191 - SEL$2E10BFB3 / RA_BATCH_SOURCES_ALL@SEL$84
 192 - SEL$2E10BFB3 / RA_BATCH_SOURCES_ALL@SEL$84
 193 - SEL$2E10BFB3 / HCA@SEL$70
 194 - SEL$2E10BFB3 / HCA@SEL$70
 195 - SEL$2E10BFB3 / HPS@SEL$70
 196 - SEL$2E10BFB3 / HPS@SEL$70
 197 - SEL$2E10BFB3 / HLO@SEL$70
 198 - SEL$2E10BFB3 / HLO@SEL$70
 199 - SEL$2E10BFB3 / RA_CUSTOMER_TRX_LINES_ALL@SEL$78
 200 - SEL$2E10BFB3 / RA_CUSTOMER_TRX_LINES_ALL@SEL$78
 201 - SEL$97       / HLA@SEL$70
 202 - SEL$97      
 204 - SEL$97       / LOC@SEL$97
 205 - SEL$97       / LOT@SEL$97
 206 - SEL$97       / LOT@SEL$97
 207 - SEL$2E10BFB3 / HAO@SEL$70
 208 - SEL$2E10BFB3 / HR_ALL_ORGANIZATION_UNITS_TL@SEL$103
 209 - SEL$2E10BFB3 / HR_ALL_ORGANIZATION_UNITS_TL@SEL$103
 210 - SEL$DE3B47A4 / CSTI@SEL$70
 211 - SEL$DE3B47A4
 212 - SEL$DE3B47A4 / FND_LOOKUP_VALUES@SEL$96
 220 - SEL$DE3B47A4 / MTL_DEFAULT_CATEGORY_SETS@SEL$93
 221 - SEL$DE3B47A4 / MTL_DEFAULT_CATEGORY_SETS@SEL$93
 222 - SEL$DE3B47A4 / CST_COST_TYPES@SEL$87
 223 - SEL$DE3B47A4 / CST_COST_TYPES@SEL$87
 224 - SEL$DE3B47A4 / CST_COST_TYPES@SEL$89
 225 - SEL$DE3B47A4 / CIC@SEL$85
 226 - SEL$DE3B47A4 / MIC@SEL$85
 227 - SEL$DE3B47A4 / MTL_SYSTEM_ITEMS_B@SEL$91
 228 - SEL$DE3B47A4 / MTL_SYSTEM_ITEMS_B@SEL$91
 229 - SEL$DE3B47A4 / T@SEL$90
 230 - SEL$2E10BFB3 / HPA@SEL$70
 231 - SEL$2E10BFB3 / HPA@SEL$70
 232 - SEL$2E10BFB3 / RA_CUST_TRX_LINE_GL_DIST_ALL@SEL$106
 233 - SEL$2E10BFB3 / RA_CUST_TRX_LINE_GL_DIST_ALL@SEL$106
 234 - SEL$2E10BFB3 / MS@SEL$70
 235 - SEL$2E10BFB3 / MS@SEL$70
 236 - SEL$2E10BFB3 / MT@SEL$70
 237 - SEL$2E10BFB3 / MT@SEL$70
 238 - SEL$2E10BFB3 / GL_CODE_COMBINATIONS@SEL$104
 239 - SEL$2E10BFB3 / GL_CODE_COMBINATIONS@SEL$104
 240 - SEL$2E10BFB3 / GL_CODE_COMBINATIONS@SEL$107
 241 - SEL$2E10BFB3 / GL_CODE_COMBINATIONS@SEL$107
 242 - SEL$863FD605 / FND_LOOKUP_VALUES@SEL$121
 243 - SEL$863FD605 / FND_LOOKUP_VALUES@SEL$121
 244 - SEL$E8E706E4 / FND_LOOKUP_VALUES@SEL$118
 245 - SEL$E8E706E4 / FND_LOOKUP_VALUES@SEL$118
 246 - SEL$B8315875 / FND_LOOKUP_VALUES@SEL$115
 247 - SEL$B8315875 / FND_LOOKUP_VALUES@SEL$115
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optimizer_undo_cost_change' '19.1.0')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('_fast_full_scan_enabled' 'false')
      OPT_PARAM('_or_expand_nvl_predicate' 'false')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_optimizer_cube_join_enabled' 'true')
      OPT_PARAM('_optimizer_hybrid_fpwj_enabled' 'true')
      OPT_PARAM('_px_replication_enabled' 'true')
      OPT_PARAM('_optimizer_partial_join_eval' 'true')
      OPT_PARAM('_px_concurrent' 'true')
      OPT_PARAM('_px_object_sampling_enabled' 'true')
      OPT_PARAM('_optimizer_unnest_scalar_sq' 'true')
      OPT_PARAM('_px_filter_parallelized' 'true')
      OPT_PARAM('_px_filter_skew_handling' 'true')
      OPT_PARAM('_optimizer_multi_table_outerjoin' 'true')
      OPT_PARAM('_px_groupby_pushdown' 'force')
      OPT_PARAM('_optimizer_ansi_join_lateral_enhance' 'true')
      OPT_PARAM('_px_parallelize_expression' 'true')
      OPT_PARAM('_optimizer_ansi_rearchitecture' 'true')
      OPT_PARAM('_optimizer_gather_stats_on_load' 'true')
      OPT_PARAM('_px_wif_dfo_declumping' 'choose')
      OPT_PARAM('_px_wif_extend_distribution_keys' 'true')
      OPT_PARAM('_px_join_skew_handling' 'true')
      OPT_PARAM('_px_partial_rollup_pushdown' 'adaptive')
      OPT_PARAM('_px_single_server_enabled' 'true')
      OPT_PARAM('_px_cpu_autodop_enabled' 'true')
      OPT_PARAM('_optimizer_use_gtt_session_stats' 'true')
      OPT_PARAM('_adaptive_window_consolidator_enabled' 'true')
      OPT_PARAM('_optimizer_null_accepting_semijoin' 'true')
      OPT_PARAM('_optimizer_gather_feedback' 'false')
      OPT_PARAM('_optimizer_cluster_by_rowid' 'true')
      OPT_PARAM('_optimizer_cluster_by_rowid_control' 129)
      OPT_PARAM('_distinct_agg_optimization_gsets' 'choose')
      OPT_PARAM('_gby_vector_aggregation_enabled' 'true')
      OPT_PARAM('_optimizer_vector_transformation' 'true')
      OPT_PARAM('_optimizer_cluster_by_rowid_batched' 'true')
      OPT_PARAM('_optimizer_inmemory_table_expansion' 'true')
      OPT_PARAM('_optimizer_inmemory_gen_pushable_preds' 'true')
      OPT_PARAM('_optimizer_inmemory_autodop' 'true')
      OPT_PARAM('_optimizer_inmemory_access_path' 'true')
      OPT_PARAM('_optimizer_inmemory_bloom_filter' 'true')
      OPT_PARAM('_px_external_table_default_stats' 'true')
      OPT_PARAM('_optimizer_inmemory_minmax_pruning' 'true')
      OPT_PARAM('_optimizer_ads_use_partial_results' 'true')
      OPT_PARAM('_optimizer_inmemory_cluster_aware_dop' 'true')
      OPT_PARAM('_query_rewrite_use_on_query_computation' 'true')
      OPT_PARAM('_px_scalable_invdist_mcol' 'true')
      OPT_PARAM('_optimizer_eliminate_subquery' 'true')
      OPT_PARAM('_sqlexec_hash_based_distagg_ssf_enabled' 'true')
      OPT_PARAM('_optimizer_union_all_gsets' 'true')
      OPT_PARAM('_optimizer_enhanced_join_elimination' 'true')
      OPT_PARAM('_optimizer_multicol_join_elimination' 'true')
      OPT_PARAM('_key_vector_create_pushdown_threshold' 20000)
      OPT_PARAM('_optimizer_enable_plsql_stats' 'true')
      OPT_PARAM('_recursive_with_parallel' 'true')
      OPT_PARAM('_recursive_with_branch_iterations' 7)
      OPT_PARAM('_px_dist_agg_partial_rollup_pushdown' 'adaptive')
      OPT_PARAM('_optimizer_key_vector_pruning_enabled' 'true')
      OPT_PARAM('_pwise_distinct_enabled' 'true')
      OPT_PARAM('_vector_encoding_mode' 'manual')
      OPT_PARAM('_ds_xt_split_count' 1)
      OPT_PARAM('_ds_sampling_method' 'PROGRESSIVE')
      OPT_PARAM('_optimizer_ads_use_spd_cache' 'true')
      OPT_PARAM('_optimizer_use_table_scanrate' 'HADOOP_ONLY')
      OPT_PARAM('_optimizer_use_xt_rowid' 'true')
      OPT_PARAM('_xt_sampling_scan_granules' 'on')
      OPT_PARAM('_optimizer_band_join_aware' 'true')
      OPT_PARAM('_optimizer_vector_base_dim_fact_factor' 200)
      OPT_PARAM('_ds_enable_view_sampling' 'true')
      OPT_PARAM('_optimizer_inmemory_use_stored_stats' 'AUTO')
      OPT_PARAM('_mv_access_compute_fresh_data' 'on')
      OPT_PARAM('_bloom_filter_ratio' 35)
      OPT_PARAM('_optimizer_control_shard_qry_processing' 65528)
      OPT_PARAM('_optimizer_interleave_or_expansion' 'true')
      OPT_PARAM('_px_nlj_bcast_rr_threshold' 10)
      OPT_PARAM('_key_vector_double_enabled' 'true')
      OPT_PARAM('_key_vector_timestamp_enabled' 'true')
      OPT_PARAM('_optimizer_key_vector_payload' 'true')
      OPT_PARAM('_bloom_pruning_setops_enabled' 'true')
      OPT_PARAM('_bloom_filter_setops_enabled' 'true')
      OPT_PARAM('_px_pwise_wif_enabled' 'true')
      OPT_PARAM('_sqlexec_reorder_wif_enabled' 'true')
      OPT_PARAM('_px_partition_skew_threshold' 80)
      OPT_PARAM('_sqlexec_pwiseops_with_sqlfuncs_enabled' 'true')
      OPT_PARAM('_sqlexec_pwiseops_with_binds_enabled' 'true')
      OPT_PARAM('_px_join_skew_null_handling' 'true')
      OPT_PARAM('_px_join_skew_use_histogram' 'true')
      OPT_PARAM('_px_join_skew_sampling_time_limit' 50)
      OPT_PARAM('_hcs_enable_pred_push' 'true')
      OPT_PARAM('_cell_offload_vector_groupby_fact_key' 'true')
      OPT_PARAM('_px_scalable_gby_invdist' 'true')
      OPT_PARAM('_px_dynamic_granules' 'true')
      OPT_PARAM('_px_dynamic_granules_adjust' 10)
      OPT_PARAM('_px_hybrid_partition_execution_enabled' 'true')
      OPT_PARAM('_px_hybrid_partition_skew_threshold' 10)
      OPT_PARAM('_cell_offload_vector_groupby_withnojoin' 'true')
      OPT_PARAM('_key_vector_join_pushdown_enabled' 'true')
      OPT_PARAM('_cell_offload_grand_total' 'true')
      OPT_PARAM('_optimizer_use_auto_indexes' 'AUTO')
      OPT_PARAM('_optimizer_quarantine_sql' 'true')
      OPT_PARAM('container_data' 'CURRENT_DICTIONARY')
      OPT_PARAM('_fix_control' '31360214:1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$641071AC")
      MERGE(@"SEL$07BDC5B4" >"SEL$2")
      OUTLINE_LEAF(@"SEL$FCA2BD42")
      UNNEST(@"SEL$68B588A0")
      OUTLINE_LEAF(@"SEL$8")
      OUTLINE_LEAF(@"SEL$3AD37D7B")
      MERGE(@"SEL$75BFBD45" >"SEL$58")
      OUTLINE_LEAF(@"SEL$BB23D85A")
      MERGE(@"SEL$7337C6A8" >"SEL$64")
      OUTLINE_LEAF(@"SEL$13566A37")
      MERGE(@"SEL$E7748E76" >"SEL$67")
      OUTLINE_LEAF(@"SEL$310B268A")
      MERGE(@"SEL$00A1922E" >"SEL$19")
      MERGE(@"SEL$1D70030A" >"SEL$19")
      MERGE(@"SEL$5EC70623" >"SEL$19")
      MERGE(@"SEL$DC3B0B0A" >"SEL$19")
      MERGE(@"SEL$FFAA604D" >"SEL$19")
      OUTLINE_LEAF(@"SEL$31")
      OUTLINE_LEAF(@"SEL$9CF1E98E")
      MERGE(@"SEL$33" >"SEL$32")
      OUTLINE_LEAF(@"SEL$141D79C4")
      MERGE(@"SEL$931DC35E" >"SEL$55")
      OUTLINE_LEAF(@"SEL$7D13FD94")
      UNNEST(@"SEL$F3E1E75B")
      OUTLINE_LEAF(@"SEL$532AF31F")
      UNNEST(@"SEL$B3E6BBC6")
      OUTLINE_LEAF(@"SEL$74")
      OUTLINE_LEAF(@"SEL$B8315875")
      MERGE(@"SEL$14966C60" >"SEL$113")
      OUTLINE_LEAF(@"SEL$E8E706E4")
      MERGE(@"SEL$1FDA56B0" >"SEL$116")
      OUTLINE_LEAF(@"SEL$863FD605")
      MERGE(@"SEL$89C8337D" >"SEL$119")
      OUTLINE_LEAF(@"SEL$DE3B47A4")
      MERGE(@"SEL$00153BA6" >"SEL$85")
      MERGE(@"SEL$4D4D2662" >"SEL$85")
      MERGE(@"SEL$93E8F980" >"SEL$85")
      MERGE(@"SEL$A80A392C" >"SEL$85")
      MERGE(@"SEL$D8170C7F" >"SEL$85")
      OUTLINE_LEAF(@"SEL$97")
      OUTLINE_LEAF(@"SEL$D790C53A")
      MERGE(@"SEL$99" >"SEL$98")
      OUTLINE_LEAF(@"SEL$E063BFF7")
      MERGE(@"SEL$101" >"SEL$100")
      OUTLINE_LEAF(@"SEL$2E10BFB3")
      UNNEST(@"SEL$B9A347D0")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$07BDC5B4")
      MERGE(@"SEL$4" >"SEL$3")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$68B588A0")
      MERGE(@"SEL$7" >"SEL$6")
      OUTLINE(@"SEL$58")
      OUTLINE(@"SEL$75BFBD45")
      MERGE(@"SEL$60" >"SEL$59")
      OUTLINE(@"SEL$64")
      OUTLINE(@"SEL$7337C6A8")
      MERGE(@"SEL$66" >"SEL$65")
      OUTLINE(@"SEL$67")
      OUTLINE(@"SEL$E7748E76")
      MERGE(@"SEL$69" >"SEL$68")
      OUTLINE(@"SEL$19")
      OUTLINE(@"SEL$00A1922E")
      MERGE(@"SEL$23" >"SEL$22")
      OUTLINE(@"SEL$1D70030A")
      MERGE(@"SEL$F7A62D4D" >"SEL$28")
      OUTLINE(@"SEL$5EC70623")
      MERGE(@"SEL$21" >"SEL$20")
      OUTLINE(@"SEL$DC3B0B0A")
      MERGE(@"SEL$25" >"SEL$24")
      OUTLINE(@"SEL$FFAA604D")
      MERGE(@"SEL$27" >"SEL$26")
      OUTLINE(@"SEL$32")
      OUTLINE(@"SEL$33")
      OUTLINE(@"SEL$55")
      OUTLINE(@"SEL$931DC35E")
      MERGE(@"SEL$57" >"SEL$56")
      OUTLINE(@"SEL$03E724CB")
      MERGE(@"SEL$0EE6DB63" >"SEL$3AE4887F")
      MERGE(@"SEL$38" >"SEL$3AE4887F")
      MERGE(@"SEL$3D91B1C1" >"SEL$3AE4887F")
      MERGE(@"SEL$41" >"SEL$3AE4887F")
      MERGE(@"SEL$42DFC41A" >"SEL$3AE4887F")
      MERGE(@"SEL$5F9076AD" >"SEL$3AE4887F")
      MERGE(@"SEL$79D9B115" >"SEL$3AE4887F")
      MERGE(@"SEL$956A709B" >"SEL$3AE4887F")
      MERGE(@"SEL$A3DCF13B" >"SEL$3AE4887F")
      MERGE(@"SEL$BE0BA760" >"SEL$3AE4887F")
      MERGE(@"SEL$D312661C" >"SEL$3AE4887F")
      MERGE(@"SEL$E8FDA8C1" >"SEL$3AE4887F")
      MERGE(@"SEL$F205D54B" >"SEL$3AE4887F")
      MERGE(@"SEL$FF4A7D68" >"SEL$3AE4887F")
      OUTLINE(@"SEL$F3E1E75B")
      MERGE(@"SEL$3B6E7E85" >"SEL$61")
      OUTLINE(@"SEL$71")
      OUTLINE(@"SEL$B3E6BBC6")
      MERGE(@"SEL$73" >"SEL$72")
      OUTLINE(@"SEL$113")
      OUTLINE(@"SEL$14966C60")
      MERGE(@"SEL$115" >"SEL$114")
      OUTLINE(@"SEL$116")
      OUTLINE(@"SEL$1FDA56B0")
      MERGE(@"SEL$118" >"SEL$117")
      OUTLINE(@"SEL$119")
      OUTLINE(@"SEL$89C8337D")
      MERGE(@"SEL$121" >"SEL$120")
      OUTLINE(@"SEL$85")
      OUTLINE(@"SEL$00153BA6")
      MERGE(@"SEL$89" >"SEL$88")
      OUTLINE(@"SEL$4D4D2662")
      MERGE(@"SEL$93" >"SEL$92")
      OUTLINE(@"SEL$93E8F980")
      MERGE(@"SEL$FF65890A" >"SEL$94")
      OUTLINE(@"SEL$A80A392C")
      MERGE(@"SEL$91" >"SEL$90")
      OUTLINE(@"SEL$D8170C7F")
      MERGE(@"SEL$87" >"SEL$86")
      OUTLINE(@"SEL$98")
      OUTLINE(@"SEL$99")
      OUTLINE(@"SEL$100")
      OUTLINE(@"SEL$101")
      OUTLINE(@"SEL$7BF2D648")
      MERGE(@"SEL$104" >"SEL$558B5422")
      MERGE(@"SEL$107" >"SEL$558B5422")
      MERGE(@"SEL$137D609B" >"SEL$558B5422")
      MERGE(@"SEL$26E6612D" >"SEL$558B5422")
      MERGE(@"SEL$31C2719E" >"SEL$558B5422")
      MERGE(@"SEL$5BBBDBCA" >"SEL$558B5422")
      MERGE(@"SEL$609B08DB" >"SEL$558B5422")
      MERGE(@"SEL$84AFE1B3" >"SEL$558B5422")
      OUTLINE(@"SEL$B9A347D0")
      MERGE(@"SEL$D631A867" >"SEL$122")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$59")
      OUTLINE(@"SEL$60")
      OUTLINE(@"SEL$65")
      OUTLINE(@"SEL$66")
      OUTLINE(@"SEL$68")
      OUTLINE(@"SEL$69")
      OUTLINE(@"SEL$22")
      OUTLINE(@"SEL$23")
      OUTLINE(@"SEL$28")
      OUTLINE(@"SEL$F7A62D4D")
      MERGE(@"SEL$30" >"SEL$29")
      OUTLINE(@"SEL$20")
      OUTLINE(@"SEL$21")
      OUTLINE(@"SEL$24")
      OUTLINE(@"SEL$25")
      OUTLINE(@"SEL$26")
      OUTLINE(@"SEL$27")
      OUTLINE(@"SEL$56")
      OUTLINE(@"SEL$57")
      OUTLINE(@"SEL$3AE4887F")
      OUTER_JOIN_TO_INNER(@"SEL$1" "MS"@"SEL$1")
      OUTLINE(@"SEL$0EE6DB63")
      MERGE(@"SEL$10" >"SEL$9")
      OUTLINE(@"SEL$38")
      OUTLINE(@"SEL$3D91B1C1")
      MERGE(@"SEL$A0A2DB7D" >"SEL$50")
      OUTLINE(@"SEL$41")
      OUTLINE(@"SEL$42DFC41A")
      MERGE(@"SEL$12" >"SEL$11")
      OUTLINE(@"SEL$5F9076AD")
      MERGE(@"SEL$35" >"SEL$34")
      OUTLINE(@"SEL$79D9B115")
      MERGE(@"SEL$40" >"SEL$39")
      OUTLINE(@"SEL$956A709B")
      MERGE(@"SEL$43" >"SEL$42")
      OUTLINE(@"SEL$A3DCF13B")
      MERGE(@"SEL$1FB6C052" >"SEL$13")
      OUTLINE(@"SEL$BE0BA760")
      MERGE(@"SEL$45" >"SEL$44")
      OUTLINE(@"SEL$D312661C")
      MERGE(@"SEL$54" >"SEL$53")
      OUTLINE(@"SEL$E8FDA8C1")
      MERGE(@"SEL$DC4B4145" >"SEL$16")
      OUTLINE(@"SEL$F205D54B")
      MERGE(@"SEL$49" >"SEL$48")
      OUTLINE(@"SEL$FF4A7D68")
      MERGE(@"SEL$37" >"SEL$36")
      OUTLINE(@"SEL$61")
      OUTLINE(@"SEL$3B6E7E85")
      MERGE(@"SEL$63" >"SEL$62")
      OUTLINE(@"SEL$72")
      OUTLINE(@"SEL$73")
      OUTLINE(@"SEL$114")
      OUTLINE(@"SEL$115")
      OUTLINE(@"SEL$117")
      OUTLINE(@"SEL$118")
      OUTLINE(@"SEL$120")
      OUTLINE(@"SEL$121")
      OUTLINE(@"SEL$88")
      OUTLINE(@"SEL$89")
      OUTLINE(@"SEL$92")
      OUTLINE(@"SEL$93")
      OUTLINE(@"SEL$94")
      OUTLINE(@"SEL$FF65890A")
      MERGE(@"SEL$96" >"SEL$95")
      OUTLINE(@"SEL$90")
      OUTLINE(@"SEL$91")
      OUTLINE(@"SEL$86")
      OUTLINE(@"SEL$87")
      OUTLINE(@"SEL$558B5422")
      OUTER_JOIN_TO_INNER(@"SEL$70" "MS"@"SEL$70")
      OUTLINE(@"SEL$104")
      OUTLINE(@"SEL$107")
      OUTLINE(@"SEL$137D609B")
      MERGE(@"SEL$0E835E3A" >"SEL$79")
      OUTLINE(@"SEL$26E6612D")
      MERGE(@"SEL$78" >"SEL$77")
      OUTLINE(@"SEL$31C2719E")
      MERGE(@"SEL$7616260A" >"SEL$82")
      OUTLINE(@"SEL$5BBBDBCA")
      MERGE(@"SEL$76" >"SEL$75")
      OUTLINE(@"SEL$609B08DB")
      MERGE(@"SEL$106" >"SEL$105")
      OUTLINE(@"SEL$84AFE1B3")
      MERGE(@"SEL$103" >"SEL$102")
      OUTLINE(@"SEL$122")
      OUTLINE(@"SEL$D631A867")
      MERGE(@"SEL$124" >"SEL$123")
      OUTLINE(@"SEL$29")
      OUTLINE(@"SEL$30")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$9")
      OUTLINE(@"SEL$10")
      OUTLINE(@"SEL$50")
      OUTLINE(@"SEL$A0A2DB7D")
      MERGE(@"SEL$52" >"SEL$51")
      OUTLINE(@"SEL$11")
      OUTLINE(@"SEL$12")
      OUTLINE(@"SEL$34")
      OUTLINE(@"SEL$35")
      OUTLINE(@"SEL$39")
      OUTLINE(@"SEL$40")
      OUTLINE(@"SEL$42")
      OUTLINE(@"SEL$43")
      OUTLINE(@"SEL$13")
      OUTLINE(@"SEL$1FB6C052")
      MERGE(@"SEL$15" >"SEL$14")
      OUTLINE(@"SEL$44")
      OUTLINE(@"SEL$45")
      OUTLINE(@"SEL$53")
      OUTLINE(@"SEL$54")
      OUTLINE(@"SEL$16")
      OUTLINE(@"SEL$DC4B4145")
      MERGE(@"SEL$18" >"SEL$17")
      OUTLINE(@"SEL$48")
      OUTLINE(@"SEL$49")
      OUTLINE(@"SEL$36")
      OUTLINE(@"SEL$37")
      OUTLINE(@"SEL$62")
      OUTLINE(@"SEL$63")
      OUTLINE(@"SEL$95")
      OUTLINE(@"SEL$96")
      OUTLINE(@"SEL$70")
      OUTLINE(@"SEL$79")
      OUTLINE(@"SEL$0E835E3A")
      MERGE(@"SEL$81" >"SEL$80")
      OUTLINE(@"SEL$77")
      OUTLINE(@"
		
		
		
Re: Sql behaving badly after upgrade [message #686593 is a reply to message #686591] Tue, 18 October 2022 11:05 Go to previous messageGo to next message
nishant1987
Messages: 8
Registered: September 2022
Junior Member
SELECT DISTINCT
       CT.TRX_NUMBER
           INVOICE_NUMBER,
       CT.DOC_SEQUENCE_VALUE
           FISCAL_NUMBER,
       CL.LINE_NUMBER
           LINE_NUM,
       HPA.PARTY_NAME
           COMPANY_NAME,
       HAT.NAME
           BUSINESS_UNIT_NAME,
       LPH.SIC_CODE
           SIC_CODE,
       REPLACE (LPH.SIC_DESCRIPTION, ';', ',')
           SIC_DESCRIPTION,
       TO_CHAR (OOHA.ORDER_NUMBER)
           SO_NUMBER,
       JRS.NAME
           SALESPERSON,
       TO_CHAR (OOLA.LINE_NUMBER)
           SO_LINE_NUMBER,
       PP.SEGMENT1
           PROJECT_NUMBER,
       PT.TASK_NUMBER
           TASK_NUMBER,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (CL.DESCRIPTION, ';', ','), CHR (10), NULL),
               CHR (09),
               NULL),
           CHR (13),
           NULL)
           ITEM_DESCRIPTION,
       CL.QUANTITY_INVOICED
           QTY,
       CT.TRX_DATE
           INVOICE_DATE,
       RC.NAME
           TRANSACTION_TYPE,
       CT.INVOICE_CURRENCY_CODE
           FUNCTIONAL_CURRENCY_FC,
       NVL (CSTI.ITEM_COST, 0)
           UNIT_COST_FC,
       (NVL (CSTI.ITEM_COST, 0) * NVL (CL.QUANTITY_INVOICED, 0))
           TOTAL_COST_FC,
       DECODE (CT.INVOICE_CURRENCY_CODE,
               'BRL', CL.EXTENDED_AMOUNT,
               CL.EXTENDED_AMOUNT * CT.EXCHANGE_RATE)
           INVOICED_AMOUNT_FC,
       HLA.LOCATION_CODE
           SHIPPING_ORGANIZATION,
       MS.SEGMENT1
           ORDERED_ITEM,
       GCV_REV.CONCATENATED_SEGMENTS
           SALES_ACCOUNT,
       GCV_COST.CONCATENATED_SEGMENTS
           COST_OF_GOODS_SOLD_ACCOUNT,
       CL.GLOBAL_ATTRIBUTE2
           FISCAL_CLASSIFICATION_CODE,
       PP.SEGMENT1
           MRO_PROJECT,
       HLO.CITY
           CUSTOMER_CITY,
       CL.GLOBAL_ATTRIBUTE4
           ITEM_ORIGIN,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (MT.DESCRIPTION, ';', ','), CHR (10), NULL),
               CHR (09),
               NULL),
           CHR (13),
           NULL)
           ITEM_PORTUGUESE_DESCRIPTION,
       LPH.PORECEIPTDATE
           PO_RECEIVED_DATE,
       CL.GLOBAL_ATTRIBUTE3
           TRANSACTION_CONDITION_CLASS,
       HLO.STATE
           CUSTOMER_STATE,
       RC.GLOBAL_ATTRIBUTE3 || '|' || RC.DESCRIPTION
           TIPO_DE_FATURAMENTO,
       CSTI.COST_TYPE
           COST_TYPE,
       HCA.ACCOUNT_NUMBER
           CUSTOMER_CODE,
       CT.CUSTOMER_TRX_ID,
       DECODE (HCA.CUSTOMER_TYPE,  'R', 'Externo',  'I', 'Interno')
           TYPE_CUSTOMER,
       CT.STATUS_TRX
           STATUS_TRX,
       OTTT.NAME
           SO_ORDER_TYPE,
       RC.TYPE
           RC_TYPE,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (CT.ATTRIBUTE1, ';', ':'), CHR (10), NULL),
               CHR (13),
               NULL),
           CHR (09),
           NULL)
           FINAL_DELIVERY_DATE,
       LPL.REQUESTED_DELIVERY_DATE
           REQ_DELIVERY_DATE,
       OOLA.REQUEST_DATE
           REQ_SHIP_DATE,
       LPL.ORIGINAL_PROMISE_DATE
           ORIGINAL_PROMISED_DATE,
       OOLA.PROMISE_DATE
           PROMISE_DATE,
       OOLA.SCHEDULE_SHIP_DATE
           SCHEDULE_SHIP_DATE,
       REPLACE (OOHA.CUST_PO_NUMBER, CHR (13), NULL)
           CUSTOMER_PO,
       OTT.NAME
           LINE_TYPE,
       LPH.PROJECT_TYPE
           TIER_TYPE,
       TT.TASK_TYPE
           TASK_TYPE,
       LPH.KIND_OF_BUSINESS
           KOB_HEADER,
       LPL.KOB3
           KOB_LINE,
       FLV_L.MEANING
           SHIPPING_TERMS,
       OOLA.SHIPMENT_PRIORITY_CODE
           SHIPMENT_PRIORITY,
       OOS.NAME
           ORDER_SOURCE,
       DECODE (OOHA.SOURCE_DOCUMENT_TYPE_ID,
               16, (SELECT TO_CHAR (AQH.QUOTE_NUMBER) QUOTE_NUMBER
                      FROM APPS.ASO_QUOTE_HEADERS AQH
                     WHERE AQH.QUOTE_HEADER_ID = OOHA.SOURCE_DOCUMENT_ID),
               NULL)
           QUOTE_NUMBER,
       PP.PROJECT_TYPE
           PROJECT_TYPE,
       PP.NAME
           PROJECT_NAME,
       (SELECT LPAD (PERIOD_NUM, 2, '0')
          FROM APPS.GL_PERIOD_STATUSES
         WHERE     SET_OF_BOOKS_ID IN (SELECT ATTRIBUTE1
                                         FROM APPS.GL_SETS_OF_BOOKS
                                        WHERE SET_OF_BOOKS_ID = 443)
               AND APPLICATION_ID = 101
               AND TRUNC (CT.TRX_DATE) BETWEEN TRUNC (START_DATE)
                                           AND TRUNC (END_DATE))
           PERIOD,
       OOLA.SHIP_FROM_ORG_ID
           SHIP_FROM_ORG_ID,
       OOLA.LINE_ID
           OE_LINE_ID,
       OOLA.HEADER_ID
           OE_HEADER_ID,
       OOLA.INVENTORY_ITEM_ID
           INVENTORY_ITEM_ID,
       CL.CUSTOMER_TRX_LINE_ID
           CUSTOMER_TRX_LINE_ID,
       CT.ORG_ID
           ORG_ID,
       CT.EXCHANGE_RATE
           EXCHANGE_RATE,
       EXTENDED_AMOUNT
           EXTENDED_AMOUNT,
       GCV_REV.CODE_COMBINATION_ID
           REV_CODE_COMBINATION_ID,
       CL.WAREHOUSE_ID
           WAREHOUSE_ID,
       OOHA.END_CUSTOMER_SITE_USE_ID
           END_CUSTOMER_SITE_USE_ID,
       (SELECT LPH1.SERVICE_CLOUD_REF
          FROM APPS.XXOM_3LP_SYM_ORA_ORDER_HDR LPH1
         WHERE LPH1.HEADER_ID = OOHA.HEADER_ID)
           SERVICE_CLOUD_REF
  FROM APPS.RA_CUSTOMER_TRX               CT,
       APPS.RA_CUSTOMER_TRX_LINES         CL,
       APPS.RA_CUST_TRX_TYPES             RC,
       APPS.RA_BATCH_SOURCES              BS,
       APPS.MTL_SYSTEM_ITEMS_B            MS,
       APPS.MTL_SYSTEM_ITEMS_TL           MT,
       APPS.CST_ITEM_COST_TYPE_V          CSTI,
       APPS.HR_LOCATIONS                  HLA,
       APPS.HZ_PARTIES                    HPA,
       APPS.HZ_CUST_ACCOUNTS              HCA,
       APPS.HZ_PARTY_SITES                HPS,
       APPS.HZ_LOCATIONS                  HLO,
       APPS.HZ_CUST_ACCT_SITES            HCS,
       APPS.HZ_CUST_SITE_USES             HCU,
       APPS.HR_ALL_ORGANIZATION_UNITS     HAO,
       APPS.HR_ALL_ORGANIZATION_UNITS_TL  HAT,
       APPS.GL_CODE_COMBINATIONS_KFV      GCV_REV,
       APPS.RA_CUST_TRX_LINE_GL_DIST      RCG,
       APPS.GL_CODE_COMBINATIONS_KFV      GCV_COST,
       APPS.OE_ORDER_LINES                OOLA,
       APPS.OE_ORDER_HEADERS              OOHA,
       APPS.OE_TRANSACTION_TYPES_TL       OTTT,
       APPS.PA_PROJECTS                   PP,
       APPS.PA_TASKS                      PT,
       APPS.XXOM_3LP_SYM_ORA_ORDER_LINES  LPL,
       APPS.XXOM_3LP_SYM_ORA_ORDER_HDR    LPH,
       APPS.OE_ORDER_SOURCES              OOS,
       APPS.OE_TRANSACTION_TYPES          OTT,
       APPS.FND_LOOKUP_VALUES             FLV_L,
       APPS.JTF_RS_SALESREPS              JRS,
       APPS.AR_NOTES                      AN,
       (SELECT PPE.PROJ_ELEMENT_ID, PTT.TASK_TYPE
          FROM APPS.PA_PROJ_ELEMENTS PPE, APPS.PA_TASK_TYPES PTT
         WHERE PPE.TYPE_ID = PTT.TASK_TYPE_ID) TT
 WHERE     HPA.PARTY_ID(+) = HCA.PARTY_ID
       AND HCA.PARTY_ID(+) = HPS.PARTY_ID
       AND HPS.LOCATION_ID = HLO.LOCATION_ID(+)
       AND HPS.PARTY_SITE_ID(+) = HCS.PARTY_SITE_ID
       AND HCS.CUST_ACCT_SITE_ID(+) = HCU.CUST_ACCT_SITE_ID
       AND (   (    OOS.NAME != 'Internal'
                AND HCU.SITE_USE_ID = CT.SHIP_TO_SITE_USE_ID
                AND HCU.SITE_USE_CODE = 'SHIP_TO')
            OR (    OOS.NAME = 'Internal'
                AND HCU.SITE_USE_ID = CT.BILL_TO_SITE_USE_ID
                AND HCU.SITE_USE_CODE = 'BILL_TO'))
       AND CT.CUSTOMER_TRX_ID = CL.CUSTOMER_TRX_ID
       AND CT.COMPLETE_FLAG = 'Y'
       AND CL.LINE_TYPE = 'LINE'
       AND CT.CUST_TRX_TYPE_ID = RC.CUST_TRX_TYPE_ID
       AND RC.ORG_ID = CT.ORG_ID
       AND RC.TYPE = 'INV'
       AND (   RC.ACCOUNTING_AFFECT_FLAG = 'Y'
            OR (SELECT DECODE (FLV.TAG, 'INCLUDE', 'Y', 'N')
                  FROM FND_LOOKUP_VALUES FLV
                 WHERE     FLV.LOOKUP_TYPE = 'EMR AR SALES X FUTURE DLVY BR'
                       AND FLV.LANGUAGE = USERENV ('LANG')
                       AND FLV.ENABLED_FLAG = 'Y'
                       AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
                           TRUNC (SYSDATE)
                       AND NVL (FLV.END_DATE_ACTIVE, TRUNC (SYSDATE) + 1) >
                           TRUNC (SYSDATE)
                       AND FLV.MEANING = RC.NAME) =
               'Y')
       AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
       AND BS.ORG_ID = CT.ORG_ID
       AND BS.BATCH_SOURCE_TYPE = 'FOREIGN'
       AND CL.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID(+)
       AND CL.WAREHOUSE_ID = MS.ORGANIZATION_ID(+)
       AND CL.WAREHOUSE_ID = HLA.INVENTORY_ORGANIZATION_ID(+)
       AND HLA.INVENTORY_ORGANIZATION_ID = HAO.ORGANIZATION_ID(+)
       AND HAO.ORGANIZATION_ID = HAT.ORGANIZATION_ID(+)
       AND HAT.LANGUAGE(+) = USERENV ('LANG')
       AND CL.CUSTOMER_TRX_ID = RCG.CUSTOMER_TRX_ID
       AND CL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID
       AND RCG.CODE_COMBINATION_ID = GCV_REV.CODE_COMBINATION_ID
       AND MS.COST_OF_SALES_ACCOUNT = GCV_COST.CODE_COMBINATION_ID
       AND RCG.ACCOUNT_CLASS = 'REV'
       AND CL.INVENTORY_ITEM_ID = CSTI.INVENTORY_ITEM_ID(+)
       AND CL.WAREHOUSE_ID = CSTI.ORGANIZATION_ID(+)
       AND CSTI.COST_TYPE(+) = 'Average'
       AND OOLA.PROJECT_ID = PP.PROJECT_ID(+)
       AND OOLA.TASK_ID = PT.TASK_ID(+)
       AND PT.TASK_ID = TT.PROJ_ELEMENT_ID(+)
       AND MS.INVENTORY_ITEM_ID = MT.INVENTORY_ITEM_ID(+)
       AND MS.ORGANIZATION_ID = MT.ORGANIZATION_ID(+)
       AND MT.LANGUAGE(+) = USERENV ('LANG')
       AND NVL (HCA.CUST_ACCOUNT_ID, -1) = NVL (HCS.CUST_ACCOUNT_ID, -1)
       AND NVL (TRUNC (AN.CREATION_DATE), CT.TRX_DATE) BETWEEN :B4 AND :B3
       AND UPPER (RC.NAME) NOT IN
               (SELECT LOOKUP_CODE
                  FROM FND_LOOKUP_VALUES
                 WHERE     LOOKUP_TYPE = 'EMR_AR_SALESREP_EXC_AUT_INV'
                       AND DESCRIPTION = 'AUTO INVOICE'
                       AND LANGUAGE = USERENV ('LANG')
                       AND ENABLED_FLAG = 'Y'
                       AND SYSDATE BETWEEN START_DATE_ACTIVE
                                       AND NVL (END_DATE_ACTIVE, SYSDATE + 1)
                       AND TAG = 'HIDE')
       AND OOLA.HEADER_ID = OOHA.HEADER_ID
       AND OOHA.SALESREP_ID = JRS.SALESREP_ID(+)
       AND OOHA.HEADER_ID = LPH.HEADER_ID(+)
       AND OOLA.LINE_ID = LPL.LINE_ID(+)
       AND OOHA.ORDER_SOURCE_ID = OOS.ORDER_SOURCE_ID(+)
       AND OOLA.LINE_TYPE_ID = OTT.TRANSACTION_TYPE_ID
       AND OTT.TRANSACTION_TYPE_CODE = 'LINE'
       AND OOLA.FOB_POINT_CODE = FLV_L.LOOKUP_CODE(+)
       AND FLV_L.LOOKUP_TYPE(+) = 'FOB'
       AND FLV_L.VIEW_APPLICATION_ID(+) = 222
       AND FLV_L.LANGUAGE(+) = USERENV ('LANG')
       AND OOHA.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
       AND OTTT.LANGUAGE(+) = USERENV ('LANG')
       AND CT.ORG_ID = :B2
       AND CL.INTERFACE_LINE_CONTEXT IN
               ('ORDER ENTRY', 'PROJECTS INVOICES', 'INTERCOMPANY')
       AND TO_CHAR (OOLA.LINE_ID) =
           DECODE (CL.INTERFACE_LINE_CONTEXT,
                   'ORDER ENTRY', CL.INTERFACE_LINE_ATTRIBUTE6,
                   'PROJECTS INVOICES', CL.ATTRIBUTE11,
                   'INTERCOMPANY', CL.INTERFACE_LINE_ATTRIBUTE6)
       AND :B1 = 'Y'
       AND CT.CUSTOMER_TRX_ID = AN.CUSTOMER_TRX_ID(+)
       AND AN.NOTE_TYPE(+) = 'MAINTAIN'
       AND AN.TEXT(+) = 'NOTA EM TRANSITO'
       AND (   (NVL (
                    (SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
                       FROM FND_LOOKUP_VALUES FLV
                      WHERE     FLV.LOOKUP_TYPE =
                                'EMR AR SALES X FUTURE DLVY BR'
                            AND FLV.LANGUAGE = USERENV ('LANG')
                            AND FLV.ENABLED_FLAG = 'Y'
                            AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
                                TRUNC (SYSDATE)
                            AND NVL (FLV.END_DATE_ACTIVE,
                                     TRUNC (SYSDATE) + 1) >
                                TRUNC (SYSDATE)
                            AND FLV.MEANING = OTTT.NAME),
                    'N') =
                'N')
            OR (NVL (
                    (SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
                       FROM FND_LOOKUP_VALUES FLV
                      WHERE     FLV.LOOKUP_TYPE =
                                'EMR AR SALES X FUTURE DLVY BR'
                            AND FLV.LANGUAGE = USERENV ('LANG')
                            AND FLV.ENABLED_FLAG = 'Y'
                            AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
                                TRUNC (SYSDATE)
                            AND NVL (FLV.END_DATE_ACTIVE,
                                     TRUNC (SYSDATE) + 1) >
                                TRUNC (SYSDATE)
                            AND OTT.NAME LIKE FLV.LOOKUP_CODE
                            AND OTTT.NAME LIKE FLV.DESCRIPTION),
                    'Y') =
                'N'))
UNION ALL
SELECT CT.TRX_NUMBER
           INVOICE_NUMBER,
       CT.DOC_SEQUENCE_VALUE
           FISCAL_NUMBER,
       CL.LINE_NUMBER
           LINE_NUM,
       HPA.PARTY_NAME
           COMPANY_NAME,
       HAT.NAME
           BUSINESS_UNIT_NAME,
       NULL
           SIC_CODE,
       NULL
           SIC_DESCRIPTION,
       NVL (ORD_TYPE.ORDER_NUMBER, 'Manual Invoice-AR')
           SO_NUMBER,
       NULL
           SALESPERSON,
       TO_CHAR (CL.SALES_ORDER_LINE)
           SO_LINE_NUMBER,
       NULL
           PROJECT_NUMBER,
       NULL
           TASK_NUMBER,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (CL.DESCRIPTION, ';', ','), CHR (10), NULL),
               CHR (09),
               NULL),
           CHR (13),
           NULL)
           ITEM_DESCRIPTION,
       CL.QUANTITY_INVOICED
           QTY,
       CT.TRX_DATE
           INVOICE_DATE,
       RC.NAME
           TRANSACTION_TYPE,
       CT.INVOICE_CURRENCY_CODE
           FUNCTIONAL_CURRENCY_FC,
       NVL (CSTI.ITEM_COST, 0)
           UNIT_COST_FC,
       (NVL (CSTI.ITEM_COST, 0) * NVL (CL.QUANTITY_INVOICED, 0))
           TOTAL_COST_FC,
       DECODE (CT.INVOICE_CURRENCY_CODE,
               'BRL', CL.EXTENDED_AMOUNT,
               CL.EXTENDED_AMOUNT * CT.EXCHANGE_RATE)
           INVOICED_AMOUNT_FC,
       HLA.LOCATION_CODE
           SHIPPING_ORGANIZATION,
       MS.SEGMENT1
           ORDERED_ITEM,
       GCV_REV.CONCATENATED_SEGMENTS
           SALES_ACCOUNT,
       GCV_COST.CONCATENATED_SEGMENTS
           COST_OF_GOODS_SOLD_ACCOUNT,
       CL.GLOBAL_ATTRIBUTE2
           FISCAL_CLASSIFICATION_CODE,
       NULL
           MRO_PROJECT,
       HLO.CITY
           CUSTOMER_CITY,
       CL.GLOBAL_ATTRIBUTE4
           ITEM_ORIGIN,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (MT.DESCRIPTION, ';', ','), CHR (10), NULL),
               CHR (09),
               NULL),
           CHR (13),
           NULL)
           ITEM_PORTUGUESE_DESCRIPTION,
       NULL
           PO_RECEIVED_DATE,
       CL.GLOBAL_ATTRIBUTE3
           TRANSACTION_CONDITION_CLASS,
       HLO.STATE
           CUSTOMER_STATE,
       RC.GLOBAL_ATTRIBUTE3 || '|' || RC.DESCRIPTION
           TIPO_DE_FATURAMENTO,
       CSTI.COST_TYPE
           COST_TYPE,
       HCA.ACCOUNT_NUMBER
           CUSTOMER_CODE,
       CT.CUSTOMER_TRX_ID,
       DECODE (HCA.CUSTOMER_TYPE,  'R', 'Externo',  'I', 'Interno')
           TYPE_CUSTOMER,
       CT.STATUS_TRX,
       NVL (ORD_TYPE.NAME, 'Manual Invoice-AR')
           SO_ORDER_TYPE,
       RC.TYPE
           RC_TYPE,
       REPLACE (
           REPLACE (
               REPLACE (REPLACE (CT.ATTRIBUTE1, ';', ':'), CHR (10), NULL),
               CHR (13),
               NULL),
           CHR (09),
           NULL)
           FINAL_DELIVERY_DATE,
       NULL
           REQ_DELIVERY_DATE,
       NULL
           REQ_SHIP_DATE,
       NULL
           ORIGINAL_PROMISED_DATE,
       NULL
           PROMISE_DATE,
       NULL
           SCHEDULE_SHIP_DATE,
       NULL
           CUSTOMER_PO,
       NULL
           LINE_TYPE,
       NULL
           TIER_TYPE,
       NULL
           TASK_TYPE,
       NULL
           KOB_HEADER,
       NULL
           KOB_LINE,
       NULL
           SHIPPING_TERMS,
       NULL
           SHIPMENT_PRIORITY,
       NULL
           ORDER_SOURCE,
       NULL
           QUOTE_NUMBER,
       NULL
           PROJECT_TYPE,
       NULL
           PROJECT_NAME,
       (SELECT LPAD (PERIOD_NUM, 2, '0')
          FROM APPS.GL_PERIOD_STATUSES
         WHERE     SET_OF_BOOKS_ID IN (SELECT ATTRIBUTE1
                                         FROM APPS.GL_SETS_OF_BOOKS
                                        WHERE SET_OF_BOOKS_ID = 443)
               AND APPLICATION_ID = 101
               AND TRUNC (CT.TRX_DATE) BETWEEN TRUNC (START_DATE)
                                           AND TRUNC (END_DATE))
           PERIOD,
       CL.WAREHOUSE_ID
           SHIP_FROM_ORG_ID,
       NULL
           OE_LINE_ID,
       NULL
           OE_HEADER_ID,
       CL.INVENTORY_ITEM_ID
           INVENTORY_ITEM_ID,
       CL.CUSTOMER_TRX_LINE_ID
           CUSTOMER_TRX_LINE_ID,
       CT.ORG_ID
           ORG_ID,
       CT.EXCHANGE_RATE
           EXCHANGE_RATE,
       CL.EXTENDED_AMOUNT
           EXTENDED_AMOUNT,
       GCV_REV.CODE_COMBINATION_ID
           REV_CODE_COMBINATION_ID,
       CL.WAREHOUSE_ID
           WAREHOUSE_ID,
       NULL
           END_CUSTOMER_SITE_USE_ID,
       (SELECT LPH1.SERVICE_CLOUD_REF
          FROM APPS.XXOM_3LP_SYM_ORA_ORDER_HDR LPH1
         WHERE LPH1.HEADER_ID = ORD_TYPE.HEADER_ID)
           SERVICE_CLOUD_REF
  FROM APPS.RA_CUSTOMER_TRX               CT,
       APPS.RA_CUSTOMER_TRX_LINES         CL,
       APPS.RA_CUST_TRX_TYPES             RC,
       APPS.RA_BATCH_SOURCES              BS,
       APPS.MTL_SYSTEM_ITEMS_B            MS,
       APPS.MTL_SYSTEM_ITEMS_TL           MT,
       APPS.CST_ITEM_COST_TYPE_V          CSTI,
       APPS.HR_LOCATIONS                  HLA,
       APPS.HZ_PARTIES                    HPA,
       APPS.HZ_CUST_ACCOUNTS              HCA,
       APPS.HZ_PARTY_SITES                HPS,
       APPS.HZ_LOCATIONS                  HLO,
       APPS.HZ_CUST_ACCT_SITES            HCS,
       APPS.HZ_CUST_SITE_USES             HCU,
       APPS.HR_ALL_ORGANIZATION_UNITS     HAO,
       APPS.HR_ALL_ORGANIZATION_UNITS_TL  HAT,
       APPS.GL_CODE_COMBINATIONS_KFV      GCV_REV,
       APPS.RA_CUST_TRX_LINE_GL_DIST      RCG,
       APPS.GL_CODE_COMBINATIONS_KFV      GCV_COST,
       APPS.PA_PROJECTS                   PPA,
       APPS.AR_NOTES                      AN,
       (SELECT TO_CHAR (OOHA.ORDER_NUMBER) ORDER_NUMBER,
               OTT.NAME,
               OOHA.HEADER_ID
          FROM APPS.OE_ORDER_HEADERS OOHA, APPS.OE_TRANSACTION_TYPES_TL OTT
         WHERE     OOHA.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
               AND OTT.LANGUAGE = USERENV ('LANG')
               AND OOHA.ORG_ID = :B2) ORD_TYPE
 WHERE     CT.SHIP_TO_SITE_USE_ID = HCU.SITE_USE_ID(+)
       AND HCU.CUST_ACCT_SITE_ID = HCS.CUST_ACCT_SITE_ID(+)
       AND HCS.PARTY_SITE_ID = HPS.PARTY_SITE_ID(+)
       AND HPS.LOCATION_ID = HLO.LOCATION_ID(+)
       AND HCS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID(+)
       AND HCA.PARTY_ID = HPA.PARTY_ID(+)
       AND HCU.SITE_USE_CODE(+) = 'SHIP_TO'
       AND CT.CUSTOMER_TRX_ID = CL.CUSTOMER_TRX_ID
       AND CL.LINE_TYPE = 'LINE'
       AND CT.CUST_TRX_TYPE_ID = RC.CUST_TRX_TYPE_ID
       AND RC.ORG_ID = CT.ORG_ID
       AND (   (RC.TYPE = 'INV')
            OR (NVL (
                    (SELECT DECODE (FLV.TAG, 'INCLUDE', 'Y', 'N')
                       FROM FND_LOOKUP_VALUES FLV
                      WHERE     FLV.LOOKUP_TYPE =
                                'EMR AR SALES X FUTURE DLVY BR'
                            AND FLV.LANGUAGE = USERENV ('LANG')
                            AND FLV.ENABLED_FLAG = 'Y'
                            AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
                                TRUNC (SYSDATE)
                            AND NVL (FLV.END_DATE_ACTIVE,
                                     TRUNC (SYSDATE) + 1) >
                                TRUNC (SYSDATE)
                            AND FLV.MEANING = RC.NAME),
                    'N') =
                'Y'))
       AND NVL (
               (SELECT DECODE (FLV.TAG, 'EXCLUDE', 'Y', 'N')
                  FROM FND_LOOKUP_VALUES FLV
                 WHERE     FLV.LOOKUP_TYPE = 'EMR AR SALES X FUTURE DLVY BR'
                       AND FLV.LANGUAGE = USERENV ('LANG')
                       AND FLV.ENABLED_FLAG = 'Y'
                       AND NVL (FLV.START_DATE_ACTIVE, TRUNC (SYSDATE)) <=
                           TRUNC (SYSDATE)
                       AND NVL (FLV.END_DATE_ACTIVE, TRUNC (SYSDATE) + 1) >
                           TRUNC (SYSDATE)
                       AND FLV.MEANING = RC.NAME),
               'N') =
           'N'
       AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
       AND BS.ORG_ID = :B2
       AND BS.BATCH_SOURCE_TYPE = 'INV'
       AND CL.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID(+)
       AND CL.WAREHOUSE_ID = MS.ORGANIZATION_ID(+)
       AND CL.WAREHOUSE_ID = HLA.INVENTORY_ORGANIZATION_ID(+)
       AND HLA.INVENTORY_ORGANIZATION_ID = HAO.ORGANIZATION_ID(+)
       AND HAO.ORGANIZATION_ID = HAT.ORGANIZATION_ID(+)
       AND CL.SALES_ORDER = PPA.SEGMENT1(+)
       AND PPA.ORG_ID(+) = :B2
       AND HAT.LANGUAGE(+) = USERENV ('LANG')
       AND CT.COMPLETE_FLAG = 'Y'
       AND CL.CUSTOMER_TRX_ID = RCG.CUSTOMER_TRX_ID
       AND CL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID
       AND RCG.CODE_COMBINATION_ID = GCV_REV.CODE_COMBINATION_ID
       AND MS.COST_OF_SALES_ACCOUNT = GCV_COST.CODE_COMBINATION_ID
       AND RCG.ACCOUNT_CLASS = 'REV'
       AND CL.INVENTORY_ITEM_ID = CSTI.INVENTORY_ITEM_ID(+)
       AND CL.WAREHOUSE_ID = CSTI.ORGANIZATION_ID(+)
       AND CSTI.COST_TYPE(+) = 'Average'
       AND MS.INVENTORY_ITEM_ID = MT.INVENTORY_ITEM_ID(+)
       AND MS.ORGANIZATION_ID = MT.ORGANIZATION_ID(+)
       AND MT.LANGUAGE(+) = USERENV ('LANG')
       AND CT.CT_REFERENCE = ORD_TYPE.ORDER_NUMBER(+)
       AND CT.CUSTOMER_TRX_ID = AN.CUSTOMER_TRX_ID(+)
       AND AN.NOTE_TYPE(+) = 'MAINTAIN'
       AND AN.TEXT(+) = 'NOTA EM TRANSITO'
       AND NVL (TRUNC (AN.CREATION_DATE), CT.TRX_DATE) BETWEEN :B4 AND :B3
       AND NOT (    CT.STATUS_TRX = 'VD'
                AND RC.GLOBAL_ATTRIBUTE3 IN
                        (SELECT LOOKUP_CODE
                           FROM FND_LOOKUP_VALUES
                          WHERE     LOOKUP_TYPE =
                                    'EMR_AR_SALESREP_EXC_MAN_INV'
                                AND DESCRIPTION = 'CFOP'
                                AND LANGUAGE = USERENV ('LANG')
                                AND ENABLED_FLAG = 'Y'
                                AND SYSDATE BETWEEN START_DATE_ACTIVE
                                                AND NVL (END_DATE_ACTIVE,
                                                         SYSDATE + 1)
                                AND TAG = 'HIDE'))
       AND UPPER (RC.NAME) IN
               (SELECT LOOKUP_CODE
                  FROM FND_LOOKUP_VALUES
                 WHERE     LOOKUP_TYPE = 'EMR_AR_SALESREP_EXC_MAN_INV'
                       AND DESCRIPTION = 'MANUAL INVOICE'
                       AND LANGUAGE = USERENV ('LANG')
                       AND ENABLED_FLAG = 'Y'
                       AND SYSDATE BETWEEN START_DATE_ACTIVE
                                       AND NVL (END_DATE_ACTIVE, SYSDATE + 1)
                       AND TAG = 'SHOW')
       AND CT.ORG_ID = :B2
       AND :B5 = 'Y'
ORDER BY 1, 2

[Updated on: Tue, 18 October 2022 11:06]

Report message to a moderator

Re: Sql behaving badly after upgrade [message #686594 is a reply to message #686593] Tue, 18 October 2022 11:07 Go to previous message
nishant1987
Messages: 8
Registered: September 2022
Junior Member
SQL_ID axw3700cukf0n
--------------------
SELECT DISTINCT CT.TRX_NUMBER INVOICE_NUMBER ,CT.DOC_SEQUENCE_VALUE 
FISCAL_NUMBER ,CL.LINE_NUMBER LINE_NUM ,HPA.PARTY_NAME COMPANY_NAME 
,HAT.NAME BUSINESS_UNIT_NAME ,LPH.SIC_CODE SIC_CODE 
,REPLACE(LPH.SIC_DESCRIPTION,';',',') SIC_DESCRIPTION 
,TO_CHAR(OOHA.ORDER_NUMBER) SO_NUMBER ,JRS.NAME SALESPERSON 
,TO_CHAR(OOLA.LINE_NUMBER) SO_LINE_NUMBER ,PP.SEGMENT1 PROJECT_NUMBER 
,PT.TASK_NUMBER TASK_NUMBER ,REPLACE(REPLACE(REPLACE(REPLACE(CL.DESCRIPT
ION,';',','),CHR(10),NULL) ,CHR(09),NULL), CHR(13),NULL) 
ITEM_DESCRIPTION ,CL.QUANTITY_INVOICED QTY ,CT.TRX_DATE INVOICE_DATE 
,RC.NAME TRANSACTION_TYPE ,CT.INVOICE_CURRENCY_CODE 
FUNCTIONAL_CURRENCY_FC ,NVL(CSTI.ITEM_COST,0) UNIT_COST_FC 
,(NVL(CSTI.ITEM_COST,0) * NVL(CL.QUANTITY_INVOICED,0)) TOTAL_COST_FC 
,DECODE(CT.INVOICE_CURRENCY_CODE,'BRL',CL.EXTENDED_AMOUNT 
,CL.EXTENDED_AMOUNT * CT.EXCHANGE_RATE) INVOICED_AMOUNT_FC 
,HLA.LOCATION_CODE SHIPPING_ORGANIZATION ,MS.SEGMENT1 ORDERED_ITEM 
,GCV_REV.CONCATENATED_SEGMENTS SALES_ACCOUNT 
,GCV_COST.CONCATENATED_SEGMENTS COST_OF_GOODS_SOLD_ACCOUNT 
,CL.GLOBAL_ATTRIBUTE2 FISCAL_CLASSIFICATION_CODE ,PP.SEGMENT1 
MRO_PROJECT ,HLO.CITY CUSTOMER_CITY ,CL.GLOBAL_ATTRIBUTE4 ITEM_ORIGIN 
,REPLACE(REPLACE(REPLACE(REPLACE(MT.DESCRIPTION,';',','),CHR(10),NULL),C
HR(09),NULL), CHR(13),NULL) ITEM_PORTUGUESE_DESCRIPTION 
,LPH.PORECEIPTDATE PO_RECEIVED_DATE ,CL.GLOBAL_ATTRIBUTE3 
TRANSACTION_CONDITION_CLASS ,HLO.STATE CUSTOMER_STATE 
,RC.GLOBAL_ATTRIBUTE3||'|'||RC.DESCRIPTION TIPO_DE_FATURAMENTO 
,CSTI.COST_TYPE COST_TYPE ,HCA.ACCOUNT_NUMBER CUSTOMER_CODE 
,CT.CUSTOMER_TRX_ID ,DECODE(HCA.CUSTOMER_TYPE,'R', 'Externo', 'I', 
'Interno') TYPE_CUSTOMER ,CT.STATUS_TRX STATUS_TRX ,OTTT.NAME 
SO_ORDER_TYPE ,RC.TYPE RC_TYPE ,REPLACE(REPLACE(REPLACE(REPLACE(CT.ATTRI
BUTE1,';',':') ,CHR(10),NULL), CHR(13),NULL), CHR(09),NULL) 
FINAL_DELIVERY_DATE ,LPL.REQUESTED_DELIVERY_DATE REQ_DELIVERY_DATE 
,OOLA.REQUEST_DATE REQ_SHIP_DATE ,LPL.ORIGINAL_PROMISE_DATE 
ORIGINAL_PROMISED_DATE ,OOLA.PROMISE_DATE PROMISE_DATE 
,OOLA.SCHEDULE_SHIP_DATE SCHEDULE_SHIP_DATE 
,REPLACE(OOHA.CUST_PO_NUMBER, CHR(13), NULL ) CUSTOMER_PO ,OTT.NAME 
LINE_TYPE ,LPH.PROJECT_TYPE TIER_TYPE ,TT.TASK_TYPE TASK_TYPE 
,LPH.KIND_OF_BUSINESS KOB_HEADER ,LPL.KOB3 KOB_LINE ,FLV_L.MEANING 
SHIPPING_TERMS ,OOLA.SHIPMENT_PRIORITY_CODE SHIPMENT_PRIORITY ,OOS.NAME 
ORDER_SOURCE ,DECODE(OOHA.SOURCE_DOCUMENT_TYPE_ID,16, (SELECT 
TO_CHAR(AQH.QUOTE_NUMBER) QUOTE_NUMBER FROM APPS.ASO_QUOTE_HEADERS AQH 
WHERE AQH.QUOTE_HEADER_ID = OOHA.SOURCE_DOCUMENT_ID ) ,NULL) 
QUOTE_NUMBER ,PP.PROJECT_TYPE PROJECT_TYPE ,PP.NAME PROJECT_NAME 
,(SELECT LPAD(PERIOD_NUM,2,'0') FROM APPS.GL_PERIOD_STATUSES WHERE 
SET_OF_BOOKS_ID IN (SELECT ATTRIBUTE1 FROM APPS.GL_SETS_OF_BOOKS WHERE 
SET_OF_BOOKS_ID = 443) AND APPLICATION_ID = 101 AND TRUNC(CT.TRX_DATE) 
BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE) ) PERIOD 
,OOLA.SHIP_FROM_ORG_ID SHIP_FROM_ORG_ID ,OOLA.LINE_ID OE_LINE_ID 
,OOLA.HEADER_ID OE_HEADER_ID ,OOLA.INVENTORY_ITEM_ID INVENTORY_ITEM_ID 
,CL.CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID ,CT.ORG_ID ORG_ID 
,CT.EXCHANGE_RATE EXCHANGE_RATE ,EXTENDED_AMOUNT EXTENDED_AMOUNT 
,GCV_REV.CODE_COMBINATION_ID REV_CODE_COMBINATION_ID ,CL.WAREHOUSE_ID 
WAREHOUSE_ID ,OOHA.END_CUSTOMER_SITE_USE_ID END_CUSTOMER_SITE_USE_ID ,( 
SELECT LPH1.SERVICE_CLOUD_REF FROM APPS.XXOM_3LP_SYM_ORA_ORDER_HDR LPH1 
WHERE LPH1.HEADER_ID = OOHA.HEADER_ID ) SERVICE_CLOUD_REF FROM 
APPS.RA_CUSTOMER_TRX CT ,APPS.RA_CUSTOMER_TRX_LINES CL 
,APPS.RA_CUST_TRX_TYPES RC ,APPS.RA_BATCH_SOURCES BS 
,APPS.MTL_SYSTEM_ITEMS_B MS ,APPS.MTL_SYSTEM_ITEMS_TL MT 
,APPS.CST_ITEM_COST_TYPE_V CSTI ,APPS.HR_LOCATIONS HLA ,APPS.HZ_PARTIES 
HPA ,APPS.HZ_CUST_ACCOUNTS HCA ,APPS.HZ_PARTY_SITES HPS 
,APPS.HZ_LOCATIONS HLO ,APPS.HZ_CUST_ACCT_SITES HCS 
,APPS.HZ_CUST_SITE_USES HCU ,APPS.HR_ALL_ORGANIZATION_UNITS HAO 
,APPS.HR_ALL_ORGANIZATION_UNITS_TL HAT ,APPS.GL_CODE_COMBINATIONS_KFV 
GCV_REV ,APPS.RA_CUST_TRX_LINE_GL_DIST RCG 
,APPS.GL_CODE_COMBINATIONS_KFV GCV_COST ,APPS.OE_ORDER_LINES OOLA 
,APPS.OE_ORDER_HEADERS OOHA ,APPS.OE_TRANSACTION_TYPES_TL 
 
Plan hash value: 408338529
 
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                                |       |       |   738K(100)|          |
|   1 |  SORT ORDER BY                                               |                                |     2 |  4758 |   738K  (2)| 00:00:29 |
|   2 |   UNION-ALL                                                  |                                |       |       |            |          |
|   3 |    FILTER                                                    |                                |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID                              | ASO_QUOTE_HEADERS_ALL          |     1 |    16 |     3   (0)| 00:00:01 |
|   5 |      INDEX UNIQUE SCAN                                       | ASO_QUOTE_HEADERS_ALL_U2       |     1 |       |     2   (0)| 00:00:01 |
|   6 |     INDEX UNIQUE SCAN                                        | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
|   7 |    NESTED LOOPS                                              |                                |     1 |    37 |   103   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID                              | GL_LEDGERS                     |     1 |    10 |     1   (0)| 00:00:01 |
|   9 |      INDEX UNIQUE SCAN                                       | GL_LEDGERS_U2                  |     1 |       |     0   (0)|          |
|  10 |     TABLE ACCESS BY INDEX ROWID                              | GL_PERIOD_STATUSES             |     1 |    27 |   102   (0)| 00:00:01 |
|  11 |      INDEX RANGE SCAN                                        | GL_PERIOD_STATUSES_U2          |   270 |       |     3   (0)| 00:00:01 |
|  12 |    TABLE ACCESS BY INDEX ROWID                               | XXOM_3LP_SYM_ORA_ORDER_HDR     |     1 |     8 |     4   (0)| 00:00:01 |
|  13 |     INDEX RANGE SCAN                                         | XXOM_3LP_SYM_ORA_ORDER_HDR_N2  |     1 |       |     3   (0)| 00:00:01 |
|  14 |    HASH UNIQUE                                               |                                |     1 |  1449 |   738K  (2)| 00:00:29 |
|  15 |     FILTER                                                   |                                |       |       |            |          |
|  16 |      FILTER                                                  |                                |       |       |            |          |
|  17 |       NESTED LOOPS OUTER                                     |                                |     1 |  1449 |   738K  (2)| 00:00:29 |
|  18 |        NESTED LOOPS OUTER                                    |                                |     1 |  1413 |   738K  (2)| 00:00:29 |
|  19 |         FILTER                                               |                                |       |       |            |          |
|  20 |          NESTED LOOPS OUTER                                  |                                |     1 |  1394 |   738K  (2)| 00:00:29 |
|  21 |           NESTED LOOPS OUTER                                 |                                |     1 |  1370 |   738K  (2)| 00:00:29 |
|  22 |            NESTED LOOPS OUTER                                |                                |     1 |  1352 |   738K  (2)| 00:00:29 |
|  23 |             NESTED LOOPS                                     |                                |     1 |  1313 |   738K  (2)| 00:00:29 |
|  24 |              HASH JOIN ANTI SNA                              |                                |     1 |  1285 |   552   (1)| 00:00:01 |
|  25 |               NESTED LOOPS OUTER                             |                                |     1 |  1210 |   532   (1)| 00:00:01 |
|  26 |                NESTED LOOPS OUTER                            |                                |     1 |  1188 |   528   (1)| 00:00:01 |
|  27 |                 NESTED LOOPS OUTER                           |                                |     1 |  1109 |   524   (1)| 00:00:01 |
|  28 |                  NESTED LOOPS OUTER                          |                                |     1 |  1097 |   522   (1)| 00:00:01 |
|  29 |                   NESTED LOOPS OUTER                         |                                |     1 |  1054 |   519   (1)| 00:00:01 |
|  30 |                    NESTED LOOPS                              |                                |     1 |  1030 |   517   (1)| 00:00:01 |
|  31 |                     NESTED LOOPS OUTER                       |                                |     1 |  1000 |   516   (1)| 00:00:01 |
|  32 |                      NESTED LOOPS                            |                                |     1 |   977 |   515   (1)| 00:00:01 |
|  33 |                       NESTED LOOPS                           |                                |     1 |   912 |   513   (1)| 00:00:01 |
|  34 |                        NESTED LOOPS                          |                                |     1 |   882 |   512   (1)| 00:00:01 |
|  35 |                         NESTED LOOPS OUTER                   |                                |     1 |   866 |   511   (1)| 00:00:01 |
|  36 |                          NESTED LOOPS OUTER                  |                                |     1 |   803 |   507   (1)| 00:00:01 |
|  37 |                           NESTED LOOPS                       |                                |     1 |   758 |   503   (1)| 00:00:01 |
|  38 |                            NESTED LOOPS OUTER                |                                |     1 |   665 |   499   (1)| 00:00:01 |
|  39 |                             NESTED LOOPS                     |                                |     1 |   611 |   496   (1)| 00:00:01 |
|  40 |                              NESTED LOOPS                    |                                |     1 |   562 |   494   (1)| 00:00:01 |
|  41 |                               NESTED LOOPS                   |                                |     1 |   530 |   491   (1)| 00:00:01 |
|  42 |                                NESTED LOOPS                  |                                |     1 |   481 |   489   (1)| 00:00:01 |
|  43 |                                 NESTED LOOPS OUTER           |                                |     1 |   453 |   485   (1)| 00:00:01 |
|  44 |                                  NESTED LOOPS OUTER          |                                |     1 |   415 |   484   (1)| 00:00:01 |
|  45 |                                   NESTED LOOPS OUTER         |                                |     1 |   410 |   484   (1)| 00:00:01 |
|  46 |                                    NESTED LOOPS OUTER        |                                |     1 |   386 |   467   (1)| 00:00:01 |
|  47 |                                     NESTED LOOPS             |                                |     1 |   341 |   463   (1)| 00:00:01 |
|  48 |                                      FILTER                  |                                |       |       |            |          |
|  49 |                                       NESTED LOOPS OUTER     |                                |     1 |   258 |   458   (1)| 00:00:01 |
|  50 |                                        NESTED LOOPS          |                                |     1 |   169 |   454   (1)| 00:00:01 |
|  51 |                                         NESTED LOOPS         |                                |     3 |   282 |   451   (1)| 00:00:01 |
|  52 |                                          TABLE ACCESS FULL   | RA_BATCH_SOURCES_ALL           |     1 |    25 |   182   (1)| 00:00:01 |
|  53 |                                           INDEX UNIQUE SCAN  | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
|  54 | EX ROWID                                 TABLE ACCESS BY IND | RA_CUSTOMER_TRX_ALL            |     7 |   483 |   270   (1)| 00:00:01 |
|  55 |                                           INDEX RANGE SCAN   | RA_CUSTOMER_TRX_ALL_X5         |  1171 |       |    51   (0)| 00:00:01 |
|  56 |                                            INDEX UNIQUE SCAN | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
|  57 | X ROWID                                 TABLE ACCESS BY INDE | RA_CUST_TRX_TYPES_ALL          |     1 |    75 |     1   (0)| 00:00:01 |
|  58 |                                          INDEX UNIQUE SCAN   | RA_CUST_TRX_TYPES_U1           |     1 |       |     0   (0)|          |
|  59 |                                           INDEX UNIQUE SCAN  | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
|  60 |  ROWID                                 TABLE ACCESS BY INDEX | AR_NOTES                       |     1 |    89 |     4   (0)| 00:00:01 |
|  61 |                                         INDEX RANGE SCAN     | AR_NOTES_N3                    |     2 |       |     2   (0)| 00:00:01 |
|  62 | OWID                                 TABLE ACCESS BY INDEX R | RA_CUSTOMER_TRX_LINES_ALL      |     1 |    83 |     5   (0)| 00:00:01 |
|  63 |                                       INDEX RANGE SCAN       | RA_CUSTOMER_TRX_LINES_N14      |     7 |       |     3   (0)| 00:00:01 |
|  64 |                                     VIEW PUSHED PREDICATE    | HR_LOCATIONS                   |     1 |    45 |     4   (0)| 00:00:01 |
|  65 |                                      NESTED LOOPS            |                                |     1 |    38 |     4   (0)| 00:00:01 |
|  66 |                                       NESTED LOOPS           |                                |     1 |    38 |     4   (0)| 00:00:01 |
|  67 |  ROWID                                 TABLE ACCESS BY INDEX | HR_LOCATIONS_ALL               |     1 |    11 |     3   (0)| 00:00:01 |
|  68 |                                         INDEX RANGE SCAN     | HR_LOCATIONS_FK1               |     3 |       |     1   (0)| 00:00:01 |
|  69 |                                        INDEX UNIQUE SCAN     | HR_LOCATIONS_ALL_TL_PK         |     1 |       |     0   (0)|          |
|  70 | ROWID                                 TABLE ACCESS BY INDEX  | HR_LOCATIONS_ALL_TL            |     1 |    27 |     1   (0)| 00:00:01 |
|  71 |                                    VIEW PUSHED PREDICATE     | CST_ITEM_COST_TYPE_V           |     1 |    24 |    17   (0)| 00:00:01 |
|  72 |                                     FILTER                   |                                |       |       |            |          |
|  73 |                                      NESTED LOOPS OUTER      |                                |     1 |   171 |    17   (0)| 00:00:01 |
|  74 |                                       NESTED LOOPS           |                                |     1 |   154 |    14   (0)| 00:00:01 |
|  75 |                                        NESTED LOOPS          |                                |     1 |   141 |    14   (0)| 00:00:01 |
|  76 |                                         NESTED LOOPS         |                                |     1 |   114 |    13   (0)| 00:00:01 |
|  77 |                                          NESTED LOOPS OUTER  |                                |     1 |    95 |     9   (0)| 00:00:01 |
|  78 |                                           NESTED LOOPS       |                                |     1 |    47 |     6   (0)| 00:00:01 |
|  79 |                                            NESTED LOOPS      |                                |     1 |    33 |     4   (0)| 00:00:01 |
|  80 | INDEX ROWID                                 TABLE ACCESS BY  | MTL_DEFAULT_CATEGORY_SETS      |     1 |    17 |     1   (0)| 00:00:01 |
|  81 | AN                                           INDEX UNIQUE SC | MTL_DEFAULT_CATEGORY_SETS_U1   |     1 |       |     0   (0)|          |
|  82 | INDEX ROWID                                 TABLE ACCESS BY  | MTL_SYSTEM_ITEMS_B             |     1 |    16 |     3   (0)| 00:00:01 |
|  83 | AN                                           INDEX UNIQUE SC | MTL_SYSTEM_ITEMS_B_UX2         |     1 |       |     2   (0)| 00:00:01 |
|  84 |                                            INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_TL_U1         |     1 |    14 |     2   (0)| 00:00:01 |
|  85 |                                           INDEX RANGE SCAN   | FND_LOOKUP_VALUES_U1           |     1 |    48 |     3   (0)| 00:00:01 |
|  86 | EX ROWID                                 TABLE ACCESS BY IND | CST_ITEM_COSTS                 |     1 |    19 |     4   (0)| 00:00:01 |
|  87 |                                           INDEX RANGE SCAN   | CST_ITEM_COSTS_U1              |     1 |       |     3   (0)| 00:00:01 |
|  88 | X ROWID                                 TABLE ACCESS BY INDE | CST_COST_TYPES                 |     1 |    27 |     1   (0)| 00:00:01 |
|  89 |                                          INDEX UNIQUE SCAN   | CST_COST_TYPES_U1              |     1 |       |     0   (0)|          |
|  90 |                                        INDEX UNIQUE SCAN     | CST_COST_TYPES_U1              |     1 |    13 |     0   (0)|          |
|  91 |                                       INDEX RANGE SCAN       | MTL_ITEM_CATEGORIES_U1         |     1 |    17 |     3   (0)| 00:00:01 |
|  92 |                                   INDEX UNIQUE SCAN          | HR_ORGANIZATION_UNITS_PK       |     1 |     5 |     0   (0)|          |
|  93 |                                  TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS_TL   |     1 |    38 |     1   (0)| 00:00:01 |
|  94 |                                   INDEX UNIQUE SCAN          | HR_ALL_ORGANIZATION_UNTS_TL_PK |     1 |       |     0   (0)|          |
|  95 |                                 TABLE ACCESS BY INDEX ROWID  | RA_CUST_TRX_LINE_GL_DIST_ALL   |     1 |    28 |     4   (0)| 00:00:01 |
|  96 |                                  INDEX RANGE SCAN            | RA_CUST_TRX_LINE_GL_DIST_N1    |     1 |       |     3   (0)| 00:00:01 |
|  97 |                                TABLE ACCESS BY INDEX ROWID   | GL_CODE_COMBINATIONS           |     1 |    49 |     2   (0)| 00:00:01 |
|  98 |                                 INDEX UNIQUE SCAN            | GL_CODE_COMBINATIONS_U1        |     1 |       |     1   (0)| 00:00:01 |
|  99 |                               TABLE ACCESS BY INDEX ROWID    | MTL_SYSTEM_ITEMS_B             |     1 |    32 |     3   (0)| 00:00:01 |
| 100 |                                INDEX UNIQUE SCAN             | MTL_SYSTEM_ITEMS_B_U1          |     1 |       |     2   (0)| 00:00:01 |
| 101 |                              TABLE ACCESS BY INDEX ROWID     | GL_CODE_COMBINATIONS           |     1 |    49 |     2   (0)| 00:00:01 |
| 102 |                               INDEX UNIQUE SCAN              | GL_CODE_COMBINATIONS_U1        |     1 |       |     1   (0)| 00:00:01 |
| 103 |                             TABLE ACCESS BY INDEX ROWID      | MTL_SYSTEM_ITEMS_TL            |     1 |    54 |     3   (0)| 00:00:01 |
| 104 |                              INDEX UNIQUE SCAN               | MTL_SYSTEM_ITEMS_TL_U1         |     1 |       |     2   (0)| 00:00:01 |
| 105 |                            TABLE ACCESS BY INDEX ROWID       | OE_ORDER_LINES_ALL             |     1 |    93 |     4   (0)| 00:00:01 |
| 106 |                             INDEX RANGE SCAN                 | OE_ORDER_LINES_X3              |     1 |       |     3   (0)| 00:00:01 |
| 107 |                           VIEW PUSHED PREDICATE              | PA_PROJECTS_ALL#               |     1 |    45 |     4   (0)| 00:00:01 |
| 108 |                            FILTER                            |                                |       |       |            |          |
| 109 |                             TABLE ACCESS BY INDEX ROWID      | PA_PROJECTS_ALL                |     1 |    59 |     4   (0)| 00:00:01 |
| 110 |                              INDEX RANGE SCAN                | PA_PROJECTS_N12                |     1 |       |     3   (0)| 00:00:01 |
| 111 |                             INDEX UNIQUE SCAN                | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 112 |                          TABLE ACCESS BY INDEX ROWID         | FND_LOOKUP_VALUES              |     1 |    63 |     4   (0)| 00:00:01 |
| 113 |                           INDEX RANGE SCAN                   | FND_LOOKUP_VALUES_X99          |     1 |       |     3   (0)| 00:00:01 |
| 114 |                         TABLE ACCESS BY INDEX ROWID          | OE_TRANSACTION_TYPES_ALL       |     1 |    16 |     1   (0)| 00:00:01 |
| 115 |                          INDEX UNIQUE SCAN                   | OE_TRANSACTION_TYPES_ALL_U1    |     1 |       |     0   (0)|          |
| 116 |                        TABLE ACCESS BY INDEX ROWID           | OE_TRANSACTION_TYPES_TL        |     1 |    30 |     1   (0)| 00:00:01 |
| 117 |                         INDEX UNIQUE SCAN                    | OE_TRANSACTION_TYPES_TL_U1     |     1 |       |     0   (0)|          |
| 118 |                       TABLE ACCESS BY INDEX ROWID            | OE_ORDER_HEADERS_ALL           |     1 |    65 |     2   (0)| 00:00:01 |
| 119 |                        INDEX UNIQUE SCAN                     | OE_ORDER_HEADERS_U1            |     1 |       |     1   (0)| 00:00:01 |
| 120 |                      TABLE ACCESS BY INDEX ROWID             | OE_ORDER_SOURCES               |     1 |    23 |     1   (0)| 00:00:01 |
| 121 |                       INDEX UNIQUE SCAN                      | OE_ORDER_SOURCES_U1            |     1 |       |     0   (0)|          |
| 122 |                     TABLE ACCESS BY INDEX ROWID              | OE_TRANSACTION_TYPES_TL        |     1 |    30 |     1   (0)| 00:00:01 |
| 123 |                      INDEX UNIQUE SCAN                       | OE_TRANSACTION_TYPES_TL_U1     |     1 |       |     0   (0)|          |
| 124 |                    TABLE ACCESS BY INDEX ROWID               | JTF_RS_SALESREPS               |     1 |    24 |     2   (0)| 00:00:01 |
| 125 |                     INDEX RANGE SCAN                         | JTF_RS_SALESREPS_U1            |     1 |       |     1   (0)| 00:00:01 |
| 126 |                   TABLE ACCESS BY INDEX ROWID                | XXOM_3LP_SYM_ORA_ORDER_HDR     |     1 |    43 |     3   (0)| 00:00:01 |
| 127 |                    INDEX RANGE SCAN                          | XXOM_3LP_SYM_ORA_ORDER_HDR_N2  |     1 |       |     2   (0)| 00:00:01 |
| 128 |                  TABLE ACCESS BY INDEX ROWID                 | PA_TASKS                       |     1 |    12 |     2   (0)| 00:00:01 |
| 129 |                   INDEX UNIQUE SCAN                          | PA_TASKS_U1                    |     1 |       |     1   (0)| 00:00:01 |
| 130 |                 VIEW PUSHED PREDICATE                        |                                |     1 |    79 |     4   (0)| 00:00:01 |
| 131 |                  NESTED LOOPS                                |                                |     1 |    32 |     4   (0)| 00:00:01 |
| 132 |                   TABLE ACCESS BY INDEX ROWID                | PA_PROJ_ELEMENTS               |     1 |    10 |     3   (0)| 00:00:01 |
| 133 |                    INDEX UNIQUE SCAN                         | PA_PROJ_ELEMENTS_U1            |     1 |       |     2   (0)| 00:00:01 |
| 134 |                   TABLE ACCESS BY INDEX ROWID                | PA_TASK_TYPES                  |     1 |    22 |     1   (0)| 00:00:01 |
| 135 |                    INDEX UNIQUE SCAN                         | PA_TASK_TYPES_U1               |     1 |       |     0   (0)|          |
| 136 |                TABLE ACCESS BY INDEX ROWID                   | XXOM_3LP_SYM_ORA_ORDER_LINES   |     1 |    22 |     4   (0)| 00:00:01 |
| 137 |                 INDEX RANGE SCAN                             | XXOM_3LP_SYM_ORA_ORDER_LIN_N6  |     1 |       |     3   (0)| 00:00:01 |
| 138 |               TABLE ACCESS BY INDEX ROWID                    | FND_LOOKUP_VALUES              |     1 |    75 |    20   (0)| 00:00:01 |
| 139 |                INDEX RANGE SCAN                              | XXAR_FND_LOOKUP_VALUES_N1      |    46 |       |     4   (0)| 00:00:01 |
| 140 |              TABLE ACCESS FULL                               | HZ_CUST_SITE_USES_ALL          |     1 |    28 |   737K  (2)| 00:00:29 |
| 141 |             VIEW PUSHED PREDICATE                            | HZ_CUST_ACCT_SITES_ALL#        |     1 |    39 |     3   (0)| 00:00:01 |
| 142 |              FILTER                                          |                                |       |       |            |          |
| 143 |               TABLE ACCESS BY INDEX ROWID                    | HZ_CUST_ACCT_SITES_ALL         |     1 |    24 |     3   (0)| 00:00:01 |
| 144 |                INDEX UNIQUE SCAN                             | HZ_CUST_ACCT_SITES_U1          |     1 |       |     2   (0)| 00:00:01 |
| 145 |               INDEX UNIQUE SCAN                              | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 146 |            TABLE ACCESS BY INDEX ROWID                       | HZ_PARTY_SITES                 |     1 |    18 |     2   (0)| 00:00:01 |
| 147 |             INDEX UNIQUE SCAN                                | HZ_PARTY_SITES_U1              |     1 |       |     1   (0)| 00:00:01 |
| 148 |           TABLE ACCESS BY INDEX ROWID                        | HZ_CUST_ACCOUNTS               |     1 |    24 |     3   (0)| 00:00:01 |
| 149 |            INDEX RANGE SCAN                                  | HZ_CUST_ACCOUNTS_N2            |     1 |       |     2   (0)| 00:00:01 |
| 150 |         TABLE ACCESS BY INDEX ROWID                          | HZ_LOCATIONS                   |     1 |    19 |     2   (0)| 00:00:01 |
| 151 |          INDEX UNIQUE SCAN                                   | HZ_LOCATIONS_U1                |     1 |       |     1   (0)| 00:00:01 |
| 152 |        TABLE ACCESS BY INDEX ROWID                           | HZ_PARTIES                     |     1 |    36 |     2   (0)| 00:00:01 |
| 153 |         INDEX UNIQUE SCAN                                    | HZ_PARTIES_U1                  |     1 |       |     1   (0)| 00:00:01 |
| 154 |      INDEX UNIQUE SCAN                                       | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 155 |      INDEX UNIQUE SCAN                                       | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 156 |      INDEX UNIQUE SCAN                                       | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 157 |      INDEX UNIQUE SCAN                                       | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 158 |      INDEX UNIQUE SCAN                                       | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 159 |      INDEX UNIQUE SCAN                                       | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 160 |      TABLE ACCESS BY INDEX ROWID                             | FND_LOOKUP_VALUES              |     1 |    64 |    17   (0)| 00:00:01 |
| 161 |       INDEX RANGE SCAN                                       | FND_LOOKUP_VALUES_U2           |     1 |       |    16   (0)| 00:00:01 |
| 162 |      TABLE ACCESS BY INDEX ROWID                             | FND_LOOKUP_VALUES              |     1 |    64 |    17   (0)| 00:00:01 |
| 163 |       INDEX RANGE SCAN                                       | FND_LOOKUP_VALUES_U2           |     1 |       |    16   (0)| 00:00:01 |
| 164 |      TABLE ACCESS BY INDEX ROWID                             | FND_LOOKUP_VALUES              |     1 |    75 |    16   (0)| 00:00:01 |
| 165 |       INDEX RANGE SCAN                                       | FND_LOOKUP_VALUES_X99          |     2 |       |    13   (0)| 00:00:01 |
| 166 |    NESTED LOOPS                                              |                                |     1 |    37 |   103   (0)| 00:00:01 |
| 167 |     TABLE ACCESS BY INDEX ROWID                              | GL_LEDGERS                     |     1 |    10 |     1   (0)| 00:00:01 |
| 168 |      INDEX UNIQUE SCAN                                       | GL_LEDGERS_U2                  |     1 |       |     0   (0)|          |
| 169 |     TABLE ACCESS BY INDEX ROWID                              | GL_PERIOD_STATUSES             |     1 |    27 |   102   (0)| 00:00:01 |
| 170 |      INDEX RANGE SCAN                                        | GL_PERIOD_STATUSES_U2          |   270 |       |     3   (0)| 00:00:01 |
| 171 |    TABLE ACCESS BY INDEX ROWID                               | XXOM_3LP_SYM_ORA_ORDER_HDR     |     1 |     8 |     4   (0)| 00:00:01 |
| 172 |     INDEX RANGE SCAN                                         | XXOM_3LP_SYM_ORA_ORDER_HDR_N2  |     1 |       |     3   (0)| 00:00:01 |
| 173 |    FILTER                                                    |                                |       |       |            |          |
| 174 |     FILTER                                                   |                                |       |       |            |          |
| 175 |      NESTED LOOPS OUTER                                      |                                |     1 |   930 |   368   (1)| 00:00:01 |
| 176 |       NESTED LOOPS OUTER                                     |                                |     1 |   906 |   351   (1)| 00:00:01 |
| 177 |        NESTED LOOPS OUTER                                    |                                |     1 |   868 |   350   (1)| 00:00:01 |
| 178 |         NESTED LOOPS OUTER                                   |                                |     1 |   863 |   350   (1)| 00:00:01 |
| 179 |          NESTED LOOPS OUTER                                  |                                |     1 |   818 |   346   (1)| 00:00:01 |
| 180 |           NESTED LOOPS OUTER                                 |                                |     1 |   782 |   344   (1)| 00:00:01 |
| 181 |            NESTED LOOPS OUTER                                |                                |     1 |   758 |   342   (1)| 00:00:01 |
| 182 |             NESTED LOOPS OUTER                               |                                |     1 |   739 |   340   (1)| 00:00:01 |
| 183 |              NESTED LOOPS OUTER                              |                                |     1 |   727 |   338   (1)| 00:00:01 |
| 184 |               NESTED LOOPS OUTER                             |                                |     1 |   688 |   335   (1)| 00:00:01 |
| 185 |                NESTED LOOPS OUTER                            |                                |     1 |   673 |   331   (1)| 00:00:01 |
| 186 |                 NESTED LOOPS OUTER                           |                                |     1 |   671 |   328   (1)| 00:00:01 |
| 187 |                  NESTED LOOPS                                |                                |     1 |   619 |   322   (1)| 00:00:01 |
| 188 |                   NESTED LOOPS                               |                                |     1 |   570 |   320   (1)| 00:00:01 |
| 189 |                    NESTED LOOPS OUTER                        |                                |     1 |   542 |   316   (1)| 00:00:01 |
| 190 |                     NESTED LOOPS                             |                                |     1 |   488 |   313   (1)| 00:00:01 |
| 191 |                      NESTED LOOPS                            |                                |     1 |   439 |   311   (1)| 00:00:01 |
| 192 |                       NESTED LOOPS                           |                                |     1 |   407 |   308   (1)| 00:00:01 |
| 193 |                        FILTER                                |                                |       |       |            |          |
| 194 |                         NESTED LOOPS OUTER                   |                                |     1 |   334 |   301   (1)| 00:00:01 |
| 195 |                          NESTED LOOPS SEMI                   |                                |     1 |   245 |   297   (1)| 00:00:01 |
| 196 |                           NESTED LOOPS                       |                                |     1 |   170 |   293   (1)| 00:00:01 |
| 197 |                            NESTED LOOPS                      |                                |     3 |   291 |   290   (1)| 00:00:01 |
| 198 |                             TABLE ACCESS BY INDEX ROWID      | RA_BATCH_SOURCES_ALL           |     1 |    25 |    20   (0)| 00:00:01 |
| 199 |                              INDEX SKIP SCAN                 | RA_BATCH_SOURCES_U2            |     1 |       |    19   (0)| 00:00:01 |
| 200 |                               INDEX UNIQUE SCAN              | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 201 |                             TABLE ACCESS BY INDEX ROWID      | RA_CUSTOMER_TRX_ALL            |     7 |   504 |   270   (1)| 00:00:01 |
| 202 |                              INDEX RANGE SCAN                | RA_CUSTOMER_TRX_ALL_X5         |  1171 |       |    51   (0)| 00:00:01 |
| 203 |                               INDEX UNIQUE SCAN              | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 204 |                            TABLE ACCESS BY INDEX ROWID       | RA_CUST_TRX_TYPES_ALL          |     1 |    73 |     1   (0)| 00:00:01 |
| 205 |                             INDEX UNIQUE SCAN                | RA_CUST_TRX_TYPES_U1           |     1 |       |     0   (0)|          |
| 206 |                              INDEX UNIQUE SCAN               | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 207 |                           TABLE ACCESS BY INDEX ROWID        | FND_LOOKUP_VALUES              |     1 |    75 |     4   (0)| 00:00:01 |
| 208 |                            INDEX RANGE SCAN                  | FND_LOOKUP_VALUES_X99          |     1 |       |     3   (0)| 00:00:01 |
| 209 |                          TABLE ACCESS BY INDEX ROWID         | AR_NOTES                       |     1 |    89 |     4   (0)| 00:00:01 |
| 210 |                           INDEX RANGE SCAN                   | AR_NOTES_N3                    |     2 |       |     2   (0)| 00:00:01 |
| 211 |                        TABLE ACCESS BY INDEX ROWID           | RA_CUSTOMER_TRX_LINES_ALL      |     1 |    73 |     7   (0)| 00:00:01 |
| 212 |                         INDEX RANGE SCAN                     | RA_CUSTOMER_TRX_LINES_N14      |    10 |       |     3   (0)| 00:00:01 |
| 213 |                       TABLE ACCESS BY INDEX ROWID            | MTL_SYSTEM_ITEMS_B             |     1 |    32 |     3   (0)| 00:00:01 |
| 214 |                        INDEX UNIQUE SCAN                     | MTL_SYSTEM_ITEMS_B_U1          |     1 |       |     2   (0)| 00:00:01 |
| 215 |                      TABLE ACCESS BY INDEX ROWID             | GL_CODE_COMBINATIONS           |     1 |    49 |     2   (0)| 00:00:01 |
| 216 |                       INDEX UNIQUE SCAN                      | GL_CODE_COMBINATIONS_U1        |     1 |       |     1   (0)| 00:00:01 |
| 217 |                     TABLE ACCESS BY INDEX ROWID              | MTL_SYSTEM_ITEMS_TL            |     1 |    54 |     3   (0)| 00:00:01 |
| 218 |                      INDEX UNIQUE SCAN                       | MTL_SYSTEM_ITEMS_TL_U1         |     1 |       |     2   (0)| 00:00:01 |
| 219 |                    TABLE ACCESS BY INDEX ROWID               | RA_CUST_TRX_LINE_GL_DIST_ALL   |     1 |    28 |     4   (0)| 00:00:01 |
| 220 |                     INDEX RANGE SCAN                         | RA_CUST_TRX_LINE_GL_DIST_N1    |     1 |       |     3   (0)| 00:00:01 |
| 221 |                   TABLE ACCESS BY INDEX ROWID                | GL_CODE_COMBINATIONS           |     1 |    49 |     2   (0)| 00:00:01 |
| 222 |                    INDEX UNIQUE SCAN                         | GL_CODE_COMBINATIONS_U1        |     1 |       |     1   (0)| 00:00:01 |
| 223 |                  VIEW PUSHED PREDICATE                       |                                |     1 |    52 |     6   (0)| 00:00:01 |
| 224 |                   FILTER                                     |                                |       |       |            |          |
| 225 |                    NESTED LOOPS                              |                                |     1 |    61 |     6   (0)| 00:00:01 |
| 226 |                     NESTED LOOPS                             |                                |     1 |    61 |     6   (0)| 00:00:01 |
| 227 |                      TABLE ACCESS BY INDEX ROWID             | OE_ORDER_HEADERS_ALL           |     1 |    31 |     5   (0)| 00:00:01 |
| 228 |                       INDEX RANGE SCAN                       | OE_ORDER_HEADERS_ALL_F1        |     2 |       |     3   (0)| 00:00:01 |
| 229 |                      INDEX UNIQUE SCAN                       | OE_TRANSACTION_TYPES_TL_U1     |     1 |       |     0   (0)|          |
| 230 |                     TABLE ACCESS BY INDEX ROWID              | OE_TRANSACTION_TYPES_TL        |     1 |    30 |     1   (0)| 00:00:01 |
| 231 |                    INDEX UNIQUE SCAN                         | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 232 |                 VIEW PUSHED PREDICATE                        | PA_PROJECTS_ALL#               |     1 |     2 |     3   (0)| 00:00:01 |
| 233 |                  FILTER                                      |                                |       |       |            |          |
| 234 |                   TABLE ACCESS BY INDEX ROWID                | PA_PROJECTS_ALL                |     1 |    13 |     3   (0)| 00:00:01 |
| 235 |                    INDEX UNIQUE SCAN                         | PA_PROJECTS_U2                 |     1 |       |     2   (0)| 00:00:01 |
| 236 |                   INDEX UNIQUE SCAN                          | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 237 |                VIEW PUSHED PREDICATE                         | HZ_CUST_SITE_USES_ALL#         |     1 |    15 |     4   (0)| 00:00:01 |
| 238 |                 FILTER                                       |                                |       |       |            |          |
| 239 |                  TABLE ACCESS BY INDEX ROWID                 | HZ_CUST_SITE_USES_ALL          |     1 |    28 |     4   (0)| 00:00:01 |
| 240 |                   INDEX UNIQUE SCAN                          | HZ_CUST_SITE_USES_U1           |     1 |       |     3   (0)| 00:00:01 |
| 241 |                  INDEX UNIQUE SCAN                           | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 242 |               VIEW PUSHED PREDICATE                          | HZ_CUST_ACCT_SITES_ALL#        |     1 |    39 |     3   (0)| 00:00:01 |
| 243 |                FILTER                                        |                                |       |       |            |          |
| 244 |                 TABLE ACCESS BY INDEX ROWID                  | HZ_CUST_ACCT_SITES_ALL         |     1 |    24 |     3   (0)| 00:00:01 |
| 245 |                  INDEX UNIQUE SCAN                           | HZ_CUST_ACCT_SITES_U1          |     1 |       |     2   (0)| 00:00:01 |
| 246 |                 INDEX UNIQUE SCAN                            | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 247 |              TABLE ACCESS BY INDEX ROWID                     | HZ_PARTY_SITES                 |     1 |    12 |     2   (0)| 00:00:01 |
| 248 |               INDEX UNIQUE SCAN                              | HZ_PARTY_SITES_U1              |     1 |       |     1   (0)| 00:00:01 |
| 249 |             TABLE ACCESS BY INDEX ROWID                      | HZ_LOCATIONS                   |     1 |    19 |     2   (0)| 00:00:01 |
| 250 |              INDEX UNIQUE SCAN                               | HZ_LOCATIONS_U1                |     1 |       |     1   (0)| 00:00:01 |
| 251 |            TABLE ACCESS BY INDEX ROWID                       | HZ_CUST_ACCOUNTS               |     1 |    24 |     2   (0)| 00:00:01 |
| 252 |             INDEX UNIQUE SCAN                                | HZ_CUST_ACCOUNTS_U1            |     1 |       |     1   (0)| 00:00:01 |
| 253 |           TABLE ACCESS BY INDEX ROWID                        | HZ_PARTIES                     |     1 |    36 |     2   (0)| 00:00:01 |
| 254 |            INDEX UNIQUE SCAN                                 | HZ_PARTIES_U1                  |     1 |       |     1   (0)| 00:00:01 |
| 255 |          VIEW PUSHED PREDICATE                               | HR_LOCATIONS                   |     1 |    45 |     4   (0)| 00:00:01 |
| 256 |           NESTED LOOPS                                       |                                |     1 |    38 |     4   (0)| 00:00:01 |
| 257 |            NESTED LOOPS                                      |                                |     1 |    38 |     4   (0)| 00:00:01 |
| 258 |             TABLE ACCESS BY INDEX ROWID                      | HR_LOCATIONS_ALL               |     1 |    11 |     3   (0)| 00:00:01 |
| 259 |              INDEX RANGE SCAN                                | HR_LOCATIONS_FK1               |     3 |       |     1   (0)| 00:00:01 |
| 260 |             INDEX UNIQUE SCAN                                | HR_LOCATIONS_ALL_TL_PK         |     1 |       |     0   (0)|          |
| 261 |            TABLE ACCESS BY INDEX ROWID                       | HR_LOCATIONS_ALL_TL            |     1 |    27 |     1   (0)| 00:00:01 |
| 262 |         INDEX UNIQUE SCAN                                    | HR_ORGANIZATION_UNITS_PK       |     1 |     5 |     0   (0)|          |
| 263 |        TABLE ACCESS BY INDEX ROWID                           | HR_ALL_ORGANIZATION_UNITS_TL   |     1 |    38 |     1   (0)| 00:00:01 |
| 264 |         INDEX UNIQUE SCAN                                    | HR_ALL_ORGANIZATION_UNTS_TL_PK |     1 |       |     0   (0)|          |
| 265 |       VIEW PUSHED PREDICATE                                  | CST_ITEM_COST_TYPE_V           |     1 |    24 |    17   (0)| 00:00:01 |
| 266 |        FILTER                                                |                                |       |       |            |          |
| 267 |         NESTED LOOPS OUTER                                   |                                |     1 |   171 |    17   (0)| 00:00:01 |
| 268 |          NESTED LOOPS                                        |                                |     1 |   154 |    14   (0)| 00:00:01 |
| 269 |           NESTED LOOPS                                       |                                |     1 |   141 |    14   (0)| 00:00:01 |
| 270 |            NESTED LOOPS                                      |                                |     1 |   114 |    13   (0)| 00:00:01 |
| 271 |             NESTED LOOPS OUTER                               |                                |     1 |    95 |     9   (0)| 00:00:01 |
| 272 |              NESTED LOOPS                                    |                                |     1 |    47 |     6   (0)| 00:00:01 |
| 273 |               NESTED LOOPS                                   |                                |     1 |    33 |     4   (0)| 00:00:01 |
| 274 |                TABLE ACCESS BY INDEX ROWID                   | MTL_DEFAULT_CATEGORY_SETS      |     1 |    17 |     1   (0)| 00:00:01 |
| 275 |                 INDEX UNIQUE SCAN                            | MTL_DEFAULT_CATEGORY_SETS_U1   |     1 |       |     0   (0)|          |
| 276 |                TABLE ACCESS BY INDEX ROWID                   | MTL_SYSTEM_ITEMS_B             |     1 |    16 |     3   (0)| 00:00:01 |
| 277 |                 INDEX UNIQUE SCAN                            | MTL_SYSTEM_ITEMS_B_UX2         |     1 |       |     2   (0)| 00:00:01 |
| 278 |               INDEX UNIQUE SCAN                              | MTL_SYSTEM_ITEMS_TL_U1         |     1 |    14 |     2   (0)| 00:00:01 |
| 279 |              INDEX RANGE SCAN                                | FND_LOOKUP_VALUES_U1           |     1 |    48 |     3   (0)| 00:00:01 |
| 280 |             TABLE ACCESS BY INDEX ROWID                      | CST_ITEM_COSTS                 |     1 |    19 |     4   (0)| 00:00:01 |
| 281 |              INDEX RANGE SCAN                                | CST_ITEM_COSTS_U1              |     1 |       |     3   (0)| 00:00:01 |
| 282 |            TABLE ACCESS BY INDEX ROWID                       | CST_COST_TYPES                 |     1 |    27 |     1   (0)| 00:00:01 |
| 283 |             INDEX UNIQUE SCAN                                | CST_COST_TYPES_U1              |     1 |       |     0   (0)|          |
| 284 |           INDEX UNIQUE SCAN                                  | CST_COST_TYPES_U1              |     1 |    13 |     0   (0)|          |
| 285 |          INDEX RANGE SCAN                                    | MTL_ITEM_CATEGORIES_U1         |     1 |    17 |     3   (0)| 00:00:01 |
| 286 |     INDEX UNIQUE SCAN                                        | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 287 |     INDEX UNIQUE SCAN                                        | MO_GLOB_ORG_ACCESS_TMP_U1      |     1 |    13 |     0   (0)|          |
| 288 |     TABLE ACCESS BY INDEX ROWID                              | FND_LOOKUP_VALUES              |     1 |    75 |    20   (0)| 00:00:01 |
| 289 |      INDEX RANGE SCAN                                        | XXAR_FND_LOOKUP_VALUES_N1      |    46 |       |     4   (0)| 00:00:01 |
| 290 |     TABLE ACCESS BY INDEX ROWID                              | FND_LOOKUP_VALUES              |     1 |    64 |    17   (0)| 00:00:01 |
| 291 |      INDEX RANGE SCAN                                        | FND_LOOKUP_VALUES_U2           |     1 |       |    16   (0)| 00:00:01 |
| 292 |     TABLE ACCESS BY INDEX ROWID                              | FND_LOOKUP_VALUES              |     1 |    64 |    17   (0)| 00:00:01 |
| 293 |      INDEX RANGE SCAN                                        | FND_LOOKUP_VALUES_U2           |     1 |       |    16   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------------------------
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 112 (U - Unused (3), N - Unresolved (107), E - Syntax error (2))
-------------------------------------------------------------------------------------------
 
   0 -  SEL$CB3B74D7_1
         N -  INDEX(@"SEL$CB3B74D7_1" "HAO"@"SEL$1" ("HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "AN"@"SEL$1" ("AR_NOTES"."CUSTOMER_TRX_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "FND_LOOKUP_VALUES"@"SEL$66" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."LOOKUP_CODE" "FND_LOOKUP_VALUES"."LANGUAGE"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "FND_LOOKUP_VALUES"@"SEL$75" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."LANGUAGE"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "GL_CODE_COMBINATIONS"@"SEL$45" ("GL_CODE_COMBINATIONS"."CODE_COMBINATION_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "GL_CODE_COMBINATIONS"@"SEL$49" ("GL_CODE_COMBINATIONS"."CODE_COMBINATION_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "HCA"@"SEL$1" ("HZ_CUST_ACCOUNTS"."PARTY_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "HLO"@"SEL$1" ("HZ_LOCATIONS"."LOCATION_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "HPA"@"SEL$1" ("HZ_PARTIES"."PARTY_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "HPS"@"SEL$1" ("HZ_PARTY_SITES"."PARTY_SITE_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "HR_ALL_ORGANIZATION_UNITS_TL"@"SEL$44" ("HR_ALL_ORGANIZATION_UNITS_TL"."ORGANIZATION_ID" "HR_ALL_ORGANIZATION_UNITS_TL"."LANGUAGE" "HR_ALL_ORGANIZATION_UNITS_TL"."ZD_EDITION_NAME"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "HZ_CUST_SITE_USES_ALL"@"SEL$41" ("HZ_CUST_SITE_USES_ALL"."SITE_USE_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "JRS"@"SEL$1" ("JTF_RS_SALESREPS"."SALESREP_ID" "JTF_RS_SALESREPS"."ORG_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "LPH"@"SEL$1" ("XXOM_3LP_SYM_ORA_ORDER_HDR"."HEADER_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "LPL"@"SEL$1" ("XXOM_3LP_SYM_ORA_ORDER_LINES"."LINE_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "MS"@"SEL$1" ("MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID" "MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "MT"@"SEL$1" ("MTL_SYSTEM_ITEMS_TL"."INVENTORY_ITEM_ID" "MTL_SYSTEM_ITEMS_TL"."ORGANIZATION_ID" "MTL_SYSTEM_ITEMS_TL"."LANGUAGE"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "OE_ORDER_HEADERS_ALL"@"SEL$54" ("OE_ORDER_HEADERS_ALL"."HEADER_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "OE_ORDER_LINES_ALL"@"SEL$51" "OE_ORDER_LINES_X3")
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "OE_ORDER_SOURCES"@"SEL$60" ("OE_ORDER_SOURCES"."ORDER_SOURCE_ID" "OE_ORDER_SOURCES"."ZD_EDITION_NAME"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "OE_TRANSACTION_TYPES_ALL"@"SEL$63" ("OE_TRANSACTION_TYPES_ALL"."TRANSACTION_TYPE_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "OTTT"@"SEL$1" ("OE_TRANSACTION_TYPES_TL"."TRANSACTION_TYPE_ID" "OE_TRANSACTION_TYPES_TL"."LANGUAGE"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "PT"@"SEL$1" ("PA_TASKS"."TASK_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "RA_CUSTOMER_TRX_ALL"@"SEL$11" ("RA_CUSTOMER_TRX_ALL"."BATCH_SOURCE_ID" "RA_CUSTOMER_TRX_ALL"."ORG_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "RA_CUSTOMER_TRX_LINES_ALL"@"SEL$14" ("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID" "RA_CUSTOMER_TRX_LINES_ALL"."LINE_TYPE" "RA_CUSTOMER_TRX_LINES_ALL"."TAX_LINE_ID" "RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_CONTEXT"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "RA_CUST_TRX_LINE_GL_DIST_ALL"@"SEL$47" ("RA_CUST_TRX_LINE_GL_DIST_ALL"."CUSTOMER_TRX_LINE_ID" "RA_CUST_TRX_LINE_GL_DIST_ALL"."ACCOUNT_SET_FLAG" "RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE" "RA_CUST_TRX_LINE_GL_DIST_ALL"."ACCOUNT_CLASS"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "RA_CUST_TRX_TYPES_ALL"@"SEL$18" ("RA_CUST_TRX_TYPES_ALL"."CUST_TRX_TYPE_ID" "RA_CUST_TRX_TYPES_ALL"."ORG_ID" "RA_CUST_TRX_TYPES_ALL"."ZD_EDITION_NAME"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_1" "T"@"SEL$61" ("OE_TRANSACTION_TYPES_TL"."TRANSACTION_TYPE_ID" "OE_TRANSACTION_TYPES_TL"."LANGUAGE"))
         N -  INDEX_SS(@"SEL$CB3B74D7_1" "RA_BATCH_SOURCES_ALL"@"SEL$22" ("RA_BATCH_SOURCES_ALL"."BATCH_SOURCE_ID" "RA_BATCH_SOURCES_ALL"."ORG_ID" "RA_BATCH_SOURCES_ALL"."ZD_EDITION_NAME"))
         N -  LEADING(@"SEL$CB3B74D7_1" "RA_BATCH_SOURCES_ALL"@"SEL$22" "RA_CUSTOMER_TRX_ALL"@"SEL$11" "RA_CUST_TRX_TYPES_ALL"@"SEL$18" "AN"@"SEL$1" "RA_CUSTOMER_TRX_LINES_ALL"@"SEL$14" "HLA"@"SEL$1" "CSTI"@"SEL$1" "HAO"@"SEL$1" "HR_ALL_ORGANIZATION_UNITS_TL"@"SEL$44" "RA_CUST_TRX_LINE_GL_DIST_ALL"@"SEL$47" "HZ_CUST_SITE_USES_ALL"@"SEL$41" "HCS"@"SEL$1" "HPS"@"SEL$1" "HCA"@"SEL$1" "HLO"@"SEL$1" "HPA"@"SEL$1" "GL_CODE_COMBINATIONS"@"SEL$45" "MS"@"SEL$1" "GL_CODE_COMBINATIONS"@"SEL$49" "MT"@"SEL$1"      "OE_ORDER_LINES_ALL"@"SEL$51" "PP"@"SEL$1" "FND_LOOKUP_VALUES"@"SEL$66" "OE_TRANSACTION_TYPES_ALL"@"SEL$63" "T"@"SEL$61" "OE_ORDER_HEADERS_ALL"@"SEL$54" "OE_ORDER_SOURCES"@"SEL$60" "OTTT"@"SEL$1" "JRS"@"SEL$1" "LPH"@"SEL$1" "PT"@"SEL$1" "TT"@"SEL$1" "LPL"@"SEL$1" "FND_LOOKUP_VALUES"@"SEL$75")
         N -  USE_HASH(@"SEL$CB3B74D7_1" "FND_LOOKUP_VALUES"@"SEL$75")
         N -  USE_NL(@"SEL$CB3B74D7_1" "FND_LOOKUP_VALUES"@"SEL$66")
         N -  USE_NL(@"SEL$CB3B74D7_1" "JRS"@"SEL$1")
         N -  USE_NL(@"SEL$CB3B74D7_1" "LPH"@"SEL$1")
         N -  USE_NL(@"SEL$CB3B74D7_1" "LPL"@"SEL$1")
         N -  USE_NL(@"SEL$CB3B74D7_1" "MT"@"SEL$1")
         N -  USE_NL(@"SEL$CB3B74D7_1" "OE_ORDER_HEADERS_ALL"@"SEL$54")
         N -  USE_NL(@"SEL$CB3B74D7_1" "OE_ORDER_LINES_ALL"@"SEL$51")
         N -  USE_NL(@"SEL$CB3B74D7_1" "OE_ORDER_SOURCES"@"SEL$60")
         N -  USE_NL(@"SEL$CB3B74D7_1" "OE_TRANSACTION_TYPES_ALL"@"SEL$63")
         N -  USE_NL(@"SEL$CB3B74D7_1" "OTTT"@"SEL$1")
         N -  USE_NL(@"SEL$CB3B74D7_1" "PP"@"SEL$1")
         N -  USE_NL(@"SEL$CB3B74D7_1" "PT"@"SEL$1")
         N -  USE_NL(@"SEL$CB3B74D7_1" "T"@"SEL$61")
         N -  USE_NL(@"SEL$CB3B74D7_1" "TT"@"SEL$1")
 
   0 -  SEL$CB3B74D7_2
         N -  INDEX(@"SEL$CB3B74D7_2" "HAO"@"SEL$CB3B74D7_2" ("HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "AN"@"SEL$CB3B74D7_2" ("AR_NOTES"."CUSTOMER_TRX_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "FND_LOOKUP_VALUES_0001"@"SEL$CB3B74D7_2" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."LOOKUP_CODE" "FND_LOOKUP_VALUES"."LANGUAGE"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "FND_LOOKUP_VALUES_0002"@"SEL$CB3B74D7_2" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."LANGUAGE"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "GL_CODE_COMBINATIONS_0001"@"SEL$CB3B74D7_2" ("GL_CODE_COMBINATIONS"."CODE_COMBINATION_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "GL_CODE_COMBINATIONS_0002"@"SEL$CB3B74D7_2" ("GL_CODE_COMBINATIONS"."CODE_COMBINATION_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "HCA"@"SEL$CB3B74D7_2" ("HZ_CUST_ACCOUNTS"."PARTY_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "HLO"@"SEL$CB3B74D7_2" ("HZ_LOCATIONS"."LOCATION_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "HPA"@"SEL$CB3B74D7_2" ("HZ_PARTIES"."PARTY_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "HPS"@"SEL$CB3B74D7_2" ("HZ_PARTY_SITES"."PARTY_SITE_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "HR_ALL_ORGANIZATION_UNITS_TL"@"SEL$CB3B74D7_2" ("HR_ALL_ORGANIZATION_UNITS_TL"."ORGANIZATION_ID" "HR_ALL_ORGANIZATION_UNITS_TL"."LANGUAGE" "HR_ALL_ORGANIZATION_UNITS_TL"."ZD_EDITION_NAME"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "HZ_CUST_SITE_USES_ALL"@"SEL$CB3B74D7_2" ("HZ_CUST_SITE_USES_ALL"."SITE_USE_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "JRS"@"SEL$CB3B74D7_2" ("JTF_RS_SALESREPS"."SALESREP_ID" "JTF_RS_SALESREPS"."ORG_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "LPH"@"SEL$CB3B74D7_2" ("XXOM_3LP_SYM_ORA_ORDER_HDR"."HEADER_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "LPL"@"SEL$CB3B74D7_2" ("XXOM_3LP_SYM_ORA_ORDER_LINES"."LINE_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "MS"@"SEL$CB3B74D7_2" ("MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID" "MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "MT"@"SEL$CB3B74D7_2" ("MTL_SYSTEM_ITEMS_TL"."INVENTORY_ITEM_ID" "MTL_SYSTEM_ITEMS_TL"."ORGANIZATION_ID" "MTL_SYSTEM_ITEMS_TL"."LANGUAGE"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "OE_ORDER_HEADERS_ALL"@"SEL$CB3B74D7_2" ("OE_ORDER_HEADERS_ALL"."HEADER_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "OE_ORDER_LINES_ALL"@"SEL$CB3B74D7_2" "OE_ORDER_LINES_X3")
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "OE_ORDER_SOURCES"@"SEL$CB3B74D7_2" ("OE_ORDER_SOURCES"."ORDER_SOURCE_ID" "OE_ORDER_SOURCES"."ZD_EDITION_NAME"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "OE_TRANSACTION_TYPES_ALL"@"SEL$CB3B74D7_2" ("OE_TRANSACTION_TYPES_ALL"."TRANSACTION_TYPE_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "OTTT"@"SEL$CB3B74D7_2" ("OE_TRANSACTION_TYPES_TL"."TRANSACTION_TYPE_ID" "OE_TRANSACTION_TYPES_TL"."LANGUAGE"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "PT"@"SEL$CB3B74D7_2" ("PA_TASKS"."TASK_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "RA_CUSTOMER_TRX_ALL"@"SEL$CB3B74D7_2" ("RA_CUSTOMER_TRX_ALL"."BATCH_SOURCE_ID" "RA_CUSTOMER_TRX_ALL"."ORG_ID"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "RA_CUSTOMER_TRX_LINES_ALL"@"SEL$CB3B74D7_2" ("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID" "RA_CUSTOMER_TRX_LINES_ALL"."LINE_TYPE" "RA_CUSTOMER_TRX_LINES_ALL"."TAX_LINE_ID" "RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_CONTEXT"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "RA_CUST_TRX_LINE_GL_DIST_ALL"@"SEL$CB3B74D7_2" ("RA_CUST_TRX_LINE_GL_DIST_ALL"."CUSTOMER_TRX_LINE_ID" "RA_CUST_TRX_LINE_GL_DIST_ALL"."ACCOUNT_SET_FLAG" "RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE" "RA_CUST_TRX_LINE_GL_DIST_ALL"."ACCOUNT_CLASS"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "RA_CUST_TRX_TYPES_ALL"@"SEL$CB3B74D7_2" ("RA_CUST_TRX_TYPES_ALL"."CUST_TRX_TYPE_ID" "RA_CUST_TRX_TYPES_ALL"."ORG_ID" "RA_CUST_TRX_TYPES_ALL"."ZD_EDITION_NAME"))
         N -  INDEX_RS_ASC(@"SEL$CB3B74D7_2" "T"@"SEL$CB3B74D7_2" ("OE_TRANSACTION_TYPES_TL"."TRANSACTION_TYPE_ID" "OE_TRANSACTION_TYPES_TL"."LANGUAGE"))
         N -  INDEX_SS(@"SEL$CB3B74D7_2" "RA_BATCH_SOURCES_ALL"@"SEL$CB3B74D7_2" ("RA_BATCH_SOURCES_ALL"."BATCH_SOURCE_ID" "RA_BATCH_SOURCES_ALL"."ORG_ID" "RA_BATCH_SOURCES_ALL"."ZD_EDITION_NAME"))
         N -  USE_HASH(@"SEL$CB3B74D7_2" "FND_LOOKUP_VALUES_0002"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "AN"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "CSTI"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "FND_LOOKUP_VALUES_0001"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "GL_CODE_COMBINATIONS_0001"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "GL_CODE_COMBINATIONS_0002"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "HAO"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "HCA"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "HCS"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "HLA"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "HLO"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "HPA"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "HPS"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "HR_ALL_ORGANIZATION_UNITS_TL"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "HZ_CUST_SITE_USES_ALL"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "JRS"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "LPH"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "LPL"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "MS"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "MT"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "OE_ORDER_HEADERS_ALL"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "OE_ORDER_LINES_ALL"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "OE_ORDER_SOURCES"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "OE_TRANSACTION_TYPES_ALL"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "OTTT"@"SEL$CB3B74D7_2")
         N -  USE_NL(@"SEL$CB3B74D7_2" "PP"@"SEL$CB3B74D7_2")
         N
		
		
		
Previous Topic: any option for this query
Next Topic: gather statistics on partition table
Goto Forum:
  


Current Time: Sun Jan 29 00:17:19 CST 2023