Hi ,
one of the sql is causing performance issue because of to_char(oola.line_Id)
AND TO_CHAR(OOLA.LINE_ID) =
DECODE (CL.INTERFACE_LINE_CONTEXT,
'ORDER ENTRY', cl.INTERFACE_LINE_ATTRIBUTE6
If i remove to_char it complete in a sec but with to_char no as it bypass index.how can i handle decode and that line _id
,
'PROJECTS INVOICES', cl.ATTRIBUTE11
,
'INTERCOMPANY', cl.INTERFACE_LINE_ATTRIBUTE6)
SELECT DISTINCT
CT.TRX_NUMBER
INVOICE_NUMBER -- 01
,
CT.DOC_SEQUENCE_VALUE
FISCAL_NUMBER -- 0101 Added by RFC[WF0003198]
,
CL.LINE_NUMBER
LINE_NUM -- 02
,
HPA.PARTY_NAME
COMPANY_NAME -- 03
,
HAT.NAME
BUSINESS_UNIT_NAME -- 04
,
LPH.SIC_CODE
SIC_CODE -- 07
,
REPLACE (LPH.SIC_DESCRIPTION, ';', ',')
SIC_DESCRIPTION -- 08
,
TO_CHAR (OOHA.ORDER_NUMBER)
SO_NUMBER -- 09
,
JRS.NAME
SALESPERSON -- 10
,
TO_CHAR (OOLA.LINE_NUMBER)
SO_LINE_NUMBER -- 11
,
PP.SEGMENT1
PROJECT_NUMBER -- 12
,
PT.TASK_NUMBER
TASK_NUMBER -- 13
,
REPLACE (
REPLACE (
REPLACE (REPLACE (CL.DESCRIPTION, ';', ','), CHR (10), NULL),
CHR (09),
NULL),
CHR (13),
NULL)
ITEM_DESCRIPTION -- 14
,
CL.QUANTITY_INVOICED
QTY -- 15
,
CT.TRX_DATE
INVOICE_DATE -- 16
,
RC.NAME
TRANSACTION_TYPE -- 17
,
CT.INVOICE_CURRENCY_CODE
FUNCTIONAL_CURRENCY_FC -- 18
,
NVL (CSTI.ITEM_COST, 0)
UNIT_COST_FC -- 19
,
(NVL (CSTI.ITEM_COST, 0) * NVL (CL.QUANTITY_INVOICED, 0))
TOTAL_COST_FC -- 20
,
DECODE (CT.INVOICE_CURRENCY_CODE,
'BRL', CL.EXTENDED_AMOUNT,
CL.EXTENDED_AMOUNT * CT.EXCHANGE_RATE)
INVOICED_AMOUNT_FC -- 21
,
HLA.LOCATION_CODE
SHIPPING_ORGANIZATION -- 22
,
MS.SEGMENT1
ORDERED_ITEM -- 23
,
GCV_REV.CONCATENATED_SEGMENTS
SALES_ACCOUNT -- 24
,
GCV_COST.CONCATENATED_SEGMENTS
COST_OF_GOODS_SOLD_ACCOUNT -- 25
,
CL.GLOBAL_ATTRIBUTE2
FISCAL_CLASSIFICATION_CODE -- 26
,
PP.SEGMENT1
MRO_PROJECT -- 27
,
HLO.CITY
CUSTOMER_CITY -- 28
,
CL.GLOBAL_ATTRIBUTE4
ITEM_ORIGIN -- 29
,
REPLACE (
REPLACE (
REPLACE (REPLACE (MT.DESCRIPTION, ';', ','), CHR (10), NULL),
CHR (09),
NULL),
CHR (13),
NULL)
ITEM_PORTUGUESE_DESCRIPTION -- 30
,
LPH.PORECEIPTDATE
PO_RECEIVED_DATE -- 31
,
CL.GLOBAL_ATTRIBUTE3
TRANSACTION_CONDITION_CLASS -- 32
,
HLO.STATE
CUSTOMER_STATE -- 33
,
RC.GLOBAL_ATTRIBUTE3 || '|' || RC.DESCRIPTION
TIPO_DE_FATURAMENTO -- 34
,
CSTI.COST_TYPE
COST_TYPE -- 35
,
HCA.ACCOUNT_NUMBER
CUSTOMER_CODE -- 36
,
CT.CUSTOMER_TRX_ID -- 37
,
DECODE (HCA.CUSTOMER_TYPE, 'R', 'Externo', 'I', 'Interno')
TYPE_CUSTOMER -- 38
,
CT.STATUS_TRX
STATUS_TRX -- 39
,
OTTT.NAME
SO_ORDER_TYPE -- 40
,
RC.TYPE
RC_TYPE -- 41
,
REPLACE (
REPLACE (
REPLACE (REPLACE (CT.ATTRIBUTE1, ';', ':'), CHR (10), NULL),
CHR (13),
NULL),
CHR (09),
NULL)
FINAL_DELIVERY_DATE -- 42
,
LPL.REQUESTED_DELIVERY_DATE
REQ_DELIVERY_DATE -- 43
,
OOLA.REQUEST_DATE
REQ_SHIP_DATE -- 44
,
LPL.ORIGINAL_PROMISE_DATE
ORIGINAL_PROMISED_DATE -- 45
,
OOLA.PROMISE_DATE
PROMISE_DATE -- 46
,
OOLA.SCHEDULE_SHIP_DATE
SCHEDULE_SHIP_DATE -- 47
,
REPLACE (OOHA.CUST_PO_NUMBER, CHR (13), NULL)
CUSTOMER_PO -- 48 -- RFC WF0000501 Added to remove CRLF characters
--,OOHA.CUST_PO_NUMBER CUSTOMER_PO -- 48
,
OTT.NAME
LINE_TYPE -- 49
,
LPH.PROJECT_TYPE
TIER_TYPE -- 50
,
TT.TASK_TYPE
TASK_TYPE -- 51
,
LPH.KIND_OF_BUSINESS
KOB_HEADER -- 52
,
LPL.KOB3
KOB_LINE -- 53
,
FLV_L.MEANING
SHIPPING_TERMS -- 54
,
OOLA.SHIPMENT_PRIORITY_CODE
SHIPMENT_PRIORITY -- 55
,
OOS.NAME
ORDER_SOURCE -- 56
,
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 -- 57
,
PP.PROJECT_TYPE
PROJECT_TYPE -- 58
,
PP.NAME
PROJECT_NAME -- 59
,
(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 -- 60
---
,
OOLA.SHIP_FROM_ORG_ID
SHIP_FROM_ORG_ID -- 61
,
OOLA.LINE_ID
OE_LINE_ID -- 62
,
OOLA.HEADER_ID
OE_HEADER_ID -- 63
,
OOLA.INVENTORY_ITEM_ID
INVENTORY_ITEM_ID -- 64
,
CL.CUSTOMER_TRX_LINE_ID
CUSTOMER_TRX_LINE_ID -- 65
,
CT.ORG_ID
ORG_ID -- 66
,
CT.EXCHANGE_RATE
EXCHANGE_RATE -- 67
,
EXTENDED_AMOUNT
EXTENDED_AMOUNT -- 68
,
GCV_REV.CODE_COMBINATION_ID
REV_CODE_COMBINATION_ID -- 69
,
CL.WAREHOUSE_ID
WAREHOUSE_ID -- 70
---
,
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 --Added by ORA-EBS022425
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 /*Commented by RFC# ORA-EBS023982*/
-- AND HCA.PARTY_ID = HPS.PARTY_ID /*Commented by RFC# ORA-EBS023982*/
-- AND HPS.LOCATION_ID = HLO.LOCATION_ID /*Commented by RFC# ORA-EBS023982*/
-- AND HPS.PARTY_SITE_ID = HCS.PARTY_SITE_ID /*Commented by RFC# ORA-EBS023982*/
-- AND HCS.CUST_ACCT_SITE_ID = HCU.CUST_ACCT_SITE_ID /*Commented by RFC# ORA-EBS023982*/
-- AND HCU.SITE_USE_ID = CT.SHIP_TO_SITE_USE_ID /*Commented by RFC# ORA-EBS023982*/
-- AND HCU.SITE_USE_CODE = 'SHIP_TO' /*Commented by RFC# ORA-EBS023982*/
WHERE HPA.PARTY_ID(+) = HCA.PARTY_ID /*Added by RFC# ORA-EBS023982*/
AND HCA.PARTY_ID(+) = HPS.PARTY_ID /*Added by RFC# ORA-EBS023982*/
AND HPS.LOCATION_ID = HLO.LOCATION_ID(+) /*Added by RFC# ORA-EBS023982*/
AND HPS.PARTY_SITE_ID(+) = HCS.PARTY_SITE_ID /*Added by RFC# ORA-EBS023982*/
AND HCS.CUST_ACCT_SITE_ID(+) = HCU.CUST_ACCT_SITE_ID /*Added by RFC# ORA-EBS023982*/
AND ( /*Added by RFC# ORA-EBS023982*/
( OOS.NAME != 'Internal' /*Added by RFC# ORA-EBS023982*/
AND HCU.SITE_USE_ID = CT.SHIP_TO_SITE_USE_ID /*Added by RFC# ORA-EBS023982*/
AND HCU.SITE_USE_CODE = 'SHIP_TO') /*Added by RFC# ORA-EBS023982*/
OR /*Added by RFC# ORA-EBS023982*/
( OOS.NAME = 'Internal' /*Added by RFC# ORA-EBS023982*/
AND HCU.SITE_USE_ID = CT.BILL_TO_SITE_USE_ID /*Added by RFC# ORA-EBS023982*/
AND HCU.SITE_USE_CODE = 'BILL_TO') /*Added by RFC# ORA-EBS023982*/
) /*Added by RFC# ORA-EBS023982*/
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' -- Flag que Indica que a Invoice vai gerar Contas a Receber. Commented by RFC[WF0003198]
-- Starting Added by RFC[WF0003198]
AND ( rc.ACCOUNTING_AFFECT_FLAG = 'Y'
OR (SELECT DECODE (flv.tag, 'INCLUDE', 'Y', 'N')
FROM apps.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')
-- Ending Added by RFC[WF0003198]
AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND BS.ORG_ID = CT.ORG_ID
AND bs.BATCH_SOURCE_TYPE = 'FOREIGN' -- INV = recebimentos manuais
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(+) = 'Frozen' --COMMENTED FOR RFC#19948
AND CSTI.COST_TYPE(+) = 'Average' --ADDED FOR RFC#19948
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 HCA.CUST_ACCOUNT_ID = HCS.CUST_ACCOUNT_ID /*Commented by RFC# ORA-EBS023982*/
AND NVL (HCA.CUST_ACCOUNT_ID, -1) = NVL (HCS.CUST_ACCOUNT_ID, -1) /*Added by RFC# ORA-EBS023982*/
---
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 -- ana incluiu 08/04/15 --258
AND OTTT.LANGUAGE(+) = USERENV ('LANG')
AND CT.ORG_ID = 4719 -- L_ORG_ID
AND CT.TRX_NUMBER = '198501'
--AND CL.INTERFACE_LINE_CONTEXT IN ('ORDER ENTRY' ,'PROJECTS INVOICES' ) /*COMMENTED BY RFC ORA-EBS023982*/
--AND TO_CHAR(OOLA.LINE_ID) = DECODE(CL.INTERFACE_LINE_CONTEXT,'ORDER ENTRY', cl.INTERFACE_LINE_ATTRIBUTE6 /*COMMENTED BY RFC ORA-EBS023982*/
-- ,'PROJECTS INVOICES',cl.ATTRIBUTE11) /*COMMENTED BY RFC ORA-EBS023982*/
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 /*ADDED BY RFC ORA-EBS023982*/
,
'INTERCOMPANY', cl.INTERFACE_LINE_ATTRIBUTE6) /*ADDED BY RFC ORA-EBS023982*/
-- AND P_SALES_ORDER = 'Y'
---
AND CT.CUSTOMER_TRX_ID = AN.CUSTOMER_TRX_ID(+)
AND AN.NOTE_TYPE(+) = 'MAINTAIN'
AND AN.TEXT(+) = 'NOTA EM TRANSITO'
ORDER BY 1, 2;