Home » SQL & PL/SQL » SQL & PL/SQL » Update Query (Oracle Apps)
Update Query [message #297280] |
Wed, 30 January 2008 23:57  |
mehulmb
Messages: 25 Registered: May 2006 Location: Pune
|
Junior Member |

|
|
Hi,
I have two database instances. One is reside in India and another is at US. I have created one custom table in us instance. In that table I have taken columns from both instances. Now first of all I am inserting data only for us columns from US instances and then I am trying to update columns of Indian instance one by one using dblink. My update query is look like this :
UPDATE ARC.ARC_CRC US_CUST
SET ORDER_NUMBER = (SELECT /*+INDEX(US_CUST IND_TRX_NUMBER) */ IND_CTA.CT_REFERENCE
FROM AR.RA_CUSTOMER_TRX_ALL@QU.COM IND_CTA
WHERE IND_CTA.TRX_NUMBER = US_CUST.CTA_TRX_NUMBER
AND EXISTS(SELECT 'X'
FROM AR.RA_CUSTOMER_TRX_ALL@QU.COM IND_CTA1,ARC.ARC_CRC US_CUST
WHERE IND_CTA1.TRX_NUMBER = US_CUST.CTA_TRX_NUMBER))
I have approx. 1,00,000 data available thats y this update query takes more than 2 hours to run...
This whole process is specified by PM thats y I cannot use materialized view.
Is there any other solutions to do the same?
Thanks............
|
|
|
Re: Update Query [message #297300 is a reply to message #297280] |
Thu, 31 January 2008 01:19   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Don't insert populating some of the columns then UPDATE the others. You must INSERT all columns at once. You need to perform a JOIN.
Ross Leishman
|
|
|
|
Re: Update Query [message #297466 is a reply to message #297317] |
Thu, 31 January 2008 20:01   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
12 database links? You showed us 2.
You wouldn't have shown us only part of the problem would you? Because that would mean you would get answers to the wrong question.
|
|
|
Re: Update Query [message #297473 is a reply to message #297466] |
Thu, 31 January 2008 21:56   |
mehulmb
Messages: 25 Registered: May 2006 Location: Pune
|
Junior Member |

|
|
This is my US instance query :
SELECT US_CUST.CTA_TRX_NUMBER INVOICE_NUMBER,
US_CUST.CTA_TRX__DATE INVOICE_DATE,
US_CUST.DUE_DATE DUE_DATE,
US_CUST.DAYS_LATE DAYS_LATE,
US_CUST.GL_DATE GL_DATE,
US_CUST.AGING AGING,
IND_RCTA.CT_REFERENCE ORDER_NUMBER,
IND_OH.CUST_PO_NUMBER CUSTOMER_PO,
US_CUST.BILL_TO_CUSTOMER_NO BILL_TO_CUSTOMER_NO,
US_CUST.BILL_TO_CUSTOMER_NAME BILL_TO_CUSTOMER_NAME,
IND_HCSU.LOCATION BILL_TO_LOCATION,
US_CUST.BILL_TO_ADDRESS 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_HL.ADDRESS1 ||' ' || IND_HL.ADDRESS2 ||' '|| IND_HL.ADDRESS3 || ' ' ||IND_HL.PROVINCE||' '|| IND_HL.CITY ||' '|| IND_HL.STATE ||' '|| IND_HL.POSTAL_CODE ||' '||IND_HL.COUNTRY SHIP_TO_ADDRESS,
IND_RCTA.ATTRIBUTE15 GC_NOTE,
IND_OH.FREIGHT_CARRIER_CODE TRANSPORTER,
IND_RC.NAME REGION,
US_CUST.ORIGINAL_AMOUNT AMOUNT_DUE_ORIGINAL,
US_CUST.APPLIED_AMOUNT AMOUNT_APPLIED,
US_CUST.REMAIN_AMOUNT AMOUNT_DUE_REMAINING,
DECODE(IND_APS.INVOICE_CURRENCY_CODE,'INR',(SUM(NVL(IND_APS.AMOUNT_LINE_ITEMS_ORIGINAL,0)) * 0.98) + SUM(NVL(IND_APS.TAX_ORIGINAL,0)),(SUM(NVL(IND_APS.AMOUNT_LINE_ITEMS_ORIGINAL,0))*(NVL(IND_APS.EXCHANGE_RATE,1)) * 0.98) + (SUM(NVL(IND_APS.TAX_ORIGINAL,0))*(NVL(IND_APS.EXCHANGE_RATE,1)))) LINE_AMT_98_TAX, --ONLY NON-INR TAX AMOUNT IS CONVERTED USING EXCHANGE RATE
DECODE(IND_APS.INVOICE_CURRENCY_CODE,'INR',(SUM(NVL(IND_APS.AMOUNT_LINE_ITEMS_ORIGINAL,0))*0.02),(SUM(NVL(IND_APS.AMOUNT_LINE_ITEMS_O RIGINAL,0))*(NVL(IND_APS.EXCHANGE_RATE,1))*0.02)) "2% BILL AMOUNT"
FROM
ARC.XX_CRC_PRC_REP US_CUST,
AR.AR_PAYMENT_SCHEDULES_ALL@QU.COM IND_APS,
AR.RA_CUSTOMER_TRX_ALL@QU.COM IND_RCTA,
ONT.OE_ORDER_HEADERS_ALL@QU.COM IND_OH,
AR.HZ_CUST_SITE_USES_ALL@QU.COM IND_HCSU ,
AR.HZ_CUST_ACCOUNTS@QU.COM IND_HCA,
AR.HZ_PARTIES@QU.COM IND_HP,
AR.HZ_CUST_ACCT_SITES_ALL@QU.COM IND_HCAS,
AR.HZ_CUST_SITE_USES_ALL@QU.COM IND_HCSU1,
AR.HZ_LOCATIONS@QU.COM IND_HL,
AR.HZ_PARTY_SITES@QU.COM IND_HPS1,
AR.HZ_CUST_ACCT_SITES_ALL@QU.COM IND_HCAS1,
AR.AR_COLLECTORS@QU.COM IND_RC,
AR.HZ_CUSTOMER_PROFILES@QU.COM IND_HCP,
ONT.OE_TRANSACTION_TYPES_TL@QU.COM IND_OTT
WHERE IND_APS.TRX_NUMBER = US_CUST.CTA_TRX_NUMBER
AND IND_APS.CUSTOMER_TRX_ID = IND_RCTA.CUSTOMER_TRX_ID
AND US_CUST.CTA_TRX_NUMBER = IND_RCTA.TRX_NUMBER
AND TO_CHAR(IND_OH.ORDER_NUMBER) = IND_RCTA.CT_REFERENCE
AND IND_HCSU.SITE_USE_ID=IND_RCTA.BILL_TO_SITE_USE_ID
AND IND_HP.PARTY_ID=IND_HCA.PARTY_ID
AND IND_HCA.CUST_ACCOUNT_ID=IND_HCAS.CUST_ACCOUNT_ID
AND IND_HCAS.CUST_ACCT_SITE_ID=IND_HCSU.CUST_ACCT_SITE_ID
AND IND_HCSU1.SITE_USE_ID = IND_RCTA.SHIP_TO_SITE_USE_ID
AND IND_HL.LOCATION_ID = IND_HPS1.LOCATION_ID
AND IND_HCAS1.CUST_ACCT_SITE_ID = IND_HCSU1.CUST_ACCT_SITE_ID
AND IND_HPS1.PARTY_SITE_ID = IND_HCAS1.PARTY_SITE_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
AND IND_OTT.TRANSACTION_TYPE_ID = IND_OH.ORDER_TYPE_ID
AND IND_OTT.NAME IN ('D0M RC Imported Sale','D0M RC Indigenous Sale')
GROUP BY
US_CUST.CTA_TRX_NUMBER,
US_CUST.CTA_TRX__DATE ,
US_CUST.DUE_DATE ,
US_CUST.DAYS_LATE ,
US_CUST.GL_DATE,
US_CUST.AGING ,
US_CUST.ORIGINAL_AMOUNT,
US_CUST.APPLIED_AMOUNT ,
US_CUST.REMAIN_AMOUNT,
IND_APS.INVOICE_CURRENCY_CODE,
US_CUST.BILL_TO_CUSTOMER_NO ,
US_CUST.BILL_TO_CUSTOMER_NAME ,
US_CUST.BILL_TO_ADDRESS,
IND_RCTA.CT_REFERENCE,
IND_RCTA.ATTRIBUTE15 ,
IND_OH.CUST_PO_NUMBER,
IND_OH.FREIGHT_CARRIER_CODE,
IND_HCSU.LOCATION,
IND_HCA.ACCOUNT_NUMBER,
IND_HP.PARTY_NAME,
IND_HCSU1.LOCATION,
IND_HL.ADDRESS1 ||' ' || IND_HL.ADDRESS2 ||' '|| IND_HL.ADDRESS3 || ' ' ||IND_HL.PROVINCE||' '|| IND_HL.CITY ||' '|| IND_HL.STATE ||' '|| IND_HL.POSTAL_CODE ||' '||IND_HL.COUNTRY,
IND_RC.NAME,
IND_APS.EXCHANGE_RATE
ORDER BY 1
I have used one custome table ARC.XX_CRC_PRC_REP from US instance. Its query is :
INSERT INTO ARC.XX_CRC_PRC_REP
SELECT /*+ CHOOSE INDEX(APS AR.AR_PAYMENT_SCHEDULES_N2) */
CTA.TRX_NUMBER CTA_TRX_NUMBER,
CTA.TRX_DATE CTA_TRX__DATE,
MIN(APS.DUE_DATE) DUE_DATE,
MAX(TO_DATE('04-NOV-2007','DD-MON-RRRR') - TO_DATE(APS.DUE_DATE,'DD-MON-RRRR')) DAYS_LATE,
DECODE(MAX(SIGN(CEIL(TO_NUMBER(TO_DATE('04-NOV-2007','DD-MON-RRRR') - TO_DATE(APS.DUE_DATE,'DD-MON-RRRR'))/30))),-1,'Curr Bucket',0,'Curr Bucket',DECODE(MAX(CEIL(TO_NUMBER(TO_DATE('04-NOV-2007','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,
HCA.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NO,
HP.PARTY_NAME BILL_TO_CUSTOMER_NAME,
HL.ADDRESS1 ||' ' || HL.ADDRESS2 ||' '|| HL.ADDRESS3 || ' ' ||HL.PROVINCE||' '|| HL.CITY ||' '|| HL.STATE ||' '|| HL.POSTAL_CODE ||' '||HL.COUNTRY BILL_TO_ADDRESS,
DECODE(APS.INVOICE_CURRENCY_CODE,'INR',SUM(NVL(APS.AMOUNT_DUE_ORIGINAL,0)),SUM(NVL(APS.AMOUNT_DUE_ORIGINAL,0))*NVL(APS.EXCHANGE_RATE, 1)) ORIGINAL_AMOUNT,
DECODE(APS.INVOICE_CURRENCY_CODE,'INR',SUM(NVL(APS.AMOUNT_APPLIED,0)),SUM(NVL(APS.AMOUNT_APPLIED,0))*NVL(APS.EXCHANGE_RATE,1)) APPLIED_AMOUNT,
--SUM(NVL(APS.AMOUNT_APPLIED,0)) APPLIED_AMOUNT,
ARC.XX_GET_REMNG_AMT(APS.TRX_NUMBER,:USER_ENT_DT,APS.GL_DATE) REMAIN_AMOUNT,
HCASA.ORIG_SYSTEM_REFERENCE HCASA_ORIG_SYSTEM_REFERENCE,
HCASA.ATTRIBUTE10 HCASA_ATTB_10,
HCASA.ATTRIBUTE11 HCASA_ATTB_11,
CTLA.ATTRIBUTE11 CTLA_ATTB_11,
CTLA.ACCOUNT_CLASS,
CTLA.GL_DATE GL_DATE
FROM
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.HZ_CUST_ACCT_SITES_ALL HCASA
WHERE
CTA.ORG_ID = '22466'
AND CTTA.ORG_ID = '22466'
AND CTLA.ORG_ID = '22466'
AND HCSUA.ORG_ID = '22466'
AND APS.ORG_ID = '22466'
AND HCASA.ORG_ID = '22466'
AND CTLA.GL_DATE BETWEEN '01-OCT-2007' AND '31-OCT-2007'
AND CTLA.ACCOUNT_CLASS = 'REC'
AND CTA.CUST_TRX_TYPE_ID = CTTA.CUST_TRX_TYPE_ID
AND CTLA.CUSTOMER_TRX_ID = CTA.CUSTOMER_TRX_ID
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 HCASA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
GROUP BY
CTA.TRX_NUMBER,
CTA.TRX_DATE,
HCA.ACCOUNT_NUMBER ,
HP.PARTY_NAME ,
HL.ADDRESS1 ||' ' || HL.ADDRESS2 ||' '|| HL.ADDRESS3 || ' ' ||HL.PROVINCE||' '|| HL.CITY ||' '|| HL.STATE ||' '|| HL.POSTAL_CODE ||' '||HL.COUNTRY ,
APS.INVOICE_CURRENCY_CODE,
HCASA.ORIG_SYSTEM_REFERENCE,
HCASA.ATTRIBUTE10,
HCASA.ATTRIBUTE11,
CTLA.ATTRIBUTE11,
CTLA.ACCOUNT_CLASS,
CTLA.GL_DATE,
APS.TRX_NUMBER,
APS.GL_DATE,
APS.EXCHANGE_RATE
ORDER BY CTA.TRX_NUMBER
Now I attached that US instance tables data with Indian instance data through the dblink. All dblink tables are coming from Indian Instance.
|
|
|
Re: Update Query [message #297584 is a reply to message #297473] |
Fri, 01 February 2008 08:42  |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Please format your posts with CODE tags. This is unreadable to the point it may cause vertigo in some people.
|
|
|
Goto Forum:
Current Time: Thu Feb 13 23:24:43 CST 2025
|