One of the sql start performing bad 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