Home » Applications » Oracle Fusion Apps & E-Business Suite » Query Execution time problem (Oracle Apps 11i)
Query Execution time problem [message #293863] |
Tue, 15 January 2008 06:08  |
mehulmb
Messages: 25 Registered: May 2006 Location: Pune
|
Junior Member |

|
|
Hello guys,
I have used two instance in oracle apps that is Indian and US..now my query is taking so much time to execute it. i have used dblink with 12 tables...is there any other solution to execute the query fastest.my query is like this :
SELECT
CTA.TRX_NUMBER INVOICE_NUMBER,
CTA.TRX_DATE INVOICE_DATE,
APS.DUE_DATE DUE_DATE,
-- TO_DATE(CTLA.GL_DATE,'DD-MON-RRRR') - TO_DATE(APS.DUE_DATE,'DD-MON-RRRR') DAYS_LATE,
TO_DATE('&GL_DATE','DD-MON-RRRR') - TO_DATE(APS.DUE_DATE,'DD-MON-RRRR') DAYS_LATE,
DECODE(CEIL(TO_NUMBER(TO_DATE('&GL_DATE','DD-MON-RRRR') - TO_DATE(APS.DUE_DATE,'DD-MON-RRRR'))/30),1,'1-30 Days',2,'31-60 Days',3,'61-90 Days',4,'91-120 Days',5,'121-150 Days',6,'151-180 Days','181 Plus Days') as AGING,
IND_RCTA.CT_REFERENCE ORDER_NUMBER,
IND_OH.CUST_PO_NUMBER PO_NUMBER,
HCA.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NO,
HP.PARTY_NAME BILL_TO_CUSTOMER_NAME,
IND_HCSU.LOCATION BILL_TO_LOCATION,
HL.ADDRESS1 ||' ' || HL.ADDRESS2 ||' '|| HL.ADDRESS3 || ' ' ||HL.PROVINCE||' '|| HL.CITY ||' '|| HL.STATE ||' '|| HL.POSTAL_CODE ||' '||HL.COUNTRY BILL_TO_ADDRESS,
IND_HCA.ACCOUNT_NUMBER SHIP_TO_CUSTOMER_NO,
IND_HP.PARTY_NAME SHIP_TO_CUSTOMER_NAME,
IND_HCSU1.LOCATION SHIP_TO_LOCATION,
IND_HP.ADDRESS1 ||' ' || IND_HP.ADDRESS2 || ' '|| IND_HP.ADDRESS3 ||' '|| IND_HP.ADDRESS4 ||' '||IND_HP.CITY ||' '||IND_HP.STATE ||' '||IND_HP.POSTAL_CODE ||' '|| IND_HP.COUNTRY SHIP_TO_ADDRESS,
IND_CTLA.INTERFACE_LINE_ATTRIBUTE3 DELIVERY_ID,
IND_RCTA.INTERFACE_HEADER_ATTRIBUTE15 GC_NOTE,
IND_OH.FREIGHT_CARRIER_CODE TRANSPORTER,
--APS.AMOUNT_DUE_ORIGINAL ORIGINAL_AMOUNT,
--APS.AMOUNT_APPLIED APPLIED_AMOUNT,
--APS.AMOUNT_DUE_REMAINING REMAIN_AMOUNT,
IND_RC.DESCRIPTION REGION_DESCRIPTION,
IND_RC.NAME REGION,
IND_APS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL,
IND_APS.AMOUNT_APPLIED AMOUNT_APPLIED,
IND_APS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING,
(IND_APS.AMOUNT_DUE_ORIGINAL * 0.98) + IND_APS.TAX_ORIGINAL LINE_AMT_98_TAX,
(IND_APS.AMOUNT_DUE_ORIGINAL * 0.02) "2% BILL AMOUNT"
/*
BSA.NAME SOURCE_NAME,
CTA.CUSTOMER_TRX_ID CUSTOMER_TRX_ID,
CTTA.NAME CLASS_NAME,
DECODE(CTTA.TYPE,'CM','Credit Memo','DM','Debit Memo','Invoice') CLASS_TYPE,
CTLA.GL_DATE GL_DATE,
CTA.INVOICE_CURRENCY_CODE,
HCSUA.LOCATION LOCATION*/
FROM
AR.RA_BATCH_SOURCES_ALL BSA,
AR.RA_CUSTOMER_TRX_ALL CTA,
AR.RA_CUST_TRX_TYPES_ALL CTTA,
AR.RA_CUST_TRX_LINE_GL_DIST_ALL CTLA,
AR.HZ_PARTIES HP,
AR.HZ_CUST_ACCOUNTS HCA,
AR.HZ_CUST_SITE_USES_ALL HCSUA,
AR.HZ_LOCATIONS HL,
AR.HZ_PARTY_SITES HPS,
AR.AR_PAYMENT_SCHEDULES_ALL APS,
AR.RA_CUSTOMER_TRX_ALL@QU_CIL.CIDC.CUMMINS.COM IND_RCTA ,
AR.HZ_PARTIES@QU_CIL.CIDC.CUMMINS.COM IND_HP,
AR.HZ_PARTY_SITES@QU_CIL.CIDC.CUMMINS.COM IND_HPS ,
AR.HZ_CUST_ACCOUNTS@QU_CIL.CIDC.CUMMINS.COM IND_HCA,
AR.HZ_CUST_ACCT_SITES_ALL@QU_CIL.CIDC.CUMMINS.COM IND_HCAS,
AR.HZ_CUST_SITE_USES_ALL@QU_CIL.CIDC.CUMMINS.COM IND_HCSU ,
AR.HZ_CUST_SITE_USES_ALL@QU_CIL.CIDC.CUMMINS.COM IND_HCSU1 ,
ONT.OE_ORDER_HEADERS_ALL@QU_CIL.CIDC.CUMMINS.COM IND_OH,
AR.RA_CUSTOMER_TRX_LINES_ALL@QU_CIL.CIDC.CUMMINS.COM IND_CTLA,
AR.AR_PAYMENT_SCHEDULES_ALL@QU_CIL.CIDC.CUMMINS.COM IND_APS,
AR.AR_COLLECTORS@QU_CIL.CIDC.CUMMINS.COM IND_RC,
AR.HZ_CUSTOMER_PROFILES@QU_CIL.CIDC.CUMMINS.COM IND_HCP
WHERE
CTA.BATCH_SOURCE_ID = BSA.BATCH_SOURCE_ID
AND CTA.CUST_TRX_TYPE_ID = CTTA.CUST_TRX_TYPE_ID
AND CTLA.CUSTOMER_TRX_ID = CTA.CUSTOMER_TRX_ID
AND CTLA.ACCOUNT_CLASS = 'REC'
AND HCA.CUST_ACCOUNT_ID = CTA.BILL_TO_CUSTOMER_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND HCSUA.SITE_USE_ID = CTA.BILL_TO_SITE_USE_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_ID = HCA.PARTY_ID
AND HPS.ORIG_SYSTEM_REFERENCE = HCSUA.ORIG_SYSTEM_REFERENCE
AND APS.CUSTOMER_TRX_ID = CTLA.CUSTOMER_TRX_ID
AND IND_RCTA.TRX_NUMBER = CTA.TRX_NUMBER
AND CTA.TRX_NUMBER = '1107060005'
AND IND_HP.PARTY_ID=IND_HPS.PARTY_ID
AND IND_HP.PARTY_ID=IND_HCA.PARTY_ID
AND IND_HCA.CUST_ACCOUNT_ID=IND_HCAS.CUST_ACCOUNT_ID
AND IND_HPS.PARTY_SITE_ID=IND_HCAS.PARTY_SITE_ID
AND IND_HCAS.CUST_ACCT_SITE_ID=IND_HCSU.CUST_ACCT_SITE_ID
AND IND_HCSU.SITE_USE_ID=IND_RCTA.BILL_TO_SITE_USE_ID
AND IND_HCSU1.SITE_USE_ID = IND_RCTA.SHIP_TO_SITE_USE_ID
AND IND_OH.ORDER_NUMBER = IND_RCTA.CT_REFERENCE
AND IND_CTLA.CUSTOMER_TRX_ID = IND_RCTA.CUSTOMER_TRX_ID
AND IND_APS.CUSTOMER_TRX_ID = IND_RCTA.CUSTOMER_TRX_ID
AND IND_HCP.COLLECTOR_ID = IND_RC.COLLECTOR_ID
AND IND_HCP.CUST_ACCOUNT_ID = IND_HCA.CUST_ACCOUNT_ID
AND IND_HCP.SITE_USE_ID = IND_HCSU.SITE_USE_ID
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jul 16 04:29:08 CDT 2025
|