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  |
 |
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  |
|
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 |
|
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 |
|
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 |
|
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 |
|
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
|
|
|
|
Goto Forum:
Current Time: Fri Feb 07 02:16:20 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
