title: SQL query is taking very long time using in EBS
body: <p>I have select query below and it is taking long time for only one org_id=83 only don't know why?</p>
  
  <p>can you please help me on this? Please let me know if you need more details on this?</p>
  
  <p>I am using Oracle 11.2.1 version.</p>
  
  <pre><code>SELECT a.customer_trx_id                                      CUSTOMER_TRX_ID, 
         a.trx_number                                           TRX_NUMBER, 
         Nvl(TL.sequence_num, 1) 
         TERM_SEQUENCE_NUMBER, 
         types.TYPE                                             TRX_TYPE, 
         l_types.meaning                                        TRX_TYPE_NAME, 
         TYPES.accounting_affect_flag 
         OPEN_RECEIVABLE_FLAG, 
         a.trx_date                                             TRX_DATE, 
         ship_to_customer_id 
         SHIP_TO_CUSTOMER_ID, 
         ship_to_contact_id                                     SHIP_TO_CONTACT_ID 
         , 
         remit_to_address_id 
         REMIT_TO_ADDRESS_ID, 
         A.primary_salesrep_id 
         PRIMARY_SALESREP_ID, 
         B.account_number                                       CUSTOMER_NUMBER, 
         A.internal_notes                                       INTERNAL_NOTES, 
         A.batch_source_id                                      BATCH_SOURCE_ID, 
         A.comments                                             TRX_COMMENTS, 
         previous_customer_trx_id 
         PREVIOUS_CUSTOMER_TRX_ID, 
         ship_to_site_use_id 
         SHIP_TO_SITE_USE_ID, 
         Nvl(printing_count, 0)                                 PRINTING_COUNT, 
         printing_original_date 
         PRINTING_ORIGINAL_DATE, 
         printing_last_printed 
         PRINTING_LAST_PRINTED, 
         printing_pending                                       PRINTING_PENDING, 
         last_printed_sequence_num 
         LAST_PRINTED_SEQUENCE_NUMBER, 
         start_date_commitment 
         START_DATE_COMMITMENT, 
         end_date_commitment 
         END_DATE_COMMITMENT, 
         initial_customer_trx_id 
         INITIAL_CUSTOMER_TRX_ID, 
         A.invoice_currency_code 
         INVOICE_CURRENCY_CODE, 
         A.term_id                                              TERM_ID, 
         A.ship_date_actual                                     SHIP_DATE_ACTUAL, 
         A.ship_via                                             SHIP_VIA, 
         A.waybill_number                                       WAYBILL_NUMBER, 
         A.purchase_order 
         PURCHASE_ORDER_NUMBER, 
         A.purchase_order_revision 
         PURCHASE_ORDER_REVISION, 
         A.purchase_order_date 
         PURCHASE_ORDER_DATE, 
         P.due_date 
         TERM_DUE_DATE_FROM_PS, 
         Nvl(TL.relative_amount, 100) * ( 100 / Nvl(T.base_amount, 100) ) 
         TERM_RELATIVE_AMOUNT, 
         T.name                                                 TERM_NAME, 
         A.bill_to_customer_id 
         BILL_TO_CUSTOMER_ID, 
         A.bill_to_contact_id                                   BILL_TO_CONTACT_ID 
         , 
         A.bill_to_site_use_id 
         BILL_TO_SITE_USE_ID, 
         U_BILL.location                                        BILL_TO_LOCATION, 
         Nvl(A_BILL.translated_customer_name, PARTY.party_name) BILL_CUST_NAME, 
         Rtrim(Rpad(LOC.address1, 40))                          BILL_ADDRESS1, 
         Rtrim(Rpad(LOC.address2, 40))                          BILL_ADDRESS2, 
         Rtrim(Rpad(LOC.address3, 40))                          BILL_ADDRESS3, 
         Rtrim(Rpad(LOC.address4, 40))                          BILL_ADDRESS4, 
         LOC.city                                               BILL_CITY, 
         Nvl(LOC.state, LOC.province)                           BILL_STATE, 
         LOC.postal_code                                        BILL_POSTAL_CODE, 
         LOC.country                                            BILL_COUNTRY, 
         U_BILL.tax_reference 
         BILL_SITE_TAX_REFERENCE, 
         PARTY.tax_reference 
         BILL_CUST_TAX_REFERENCE, 
         Nvl(amount_line_items_original, To_number(NULL))       TRX_LINE_AMOUNT, 
         Nvl(tax_original, To_number(NULL))                     TRX_TAX_AMOUNT, 
         Nvl(freight_original, To_number(NULL))                 TRX_FREIGHT_AMOUNT 
         , 
         p.amount_due_original 
         TRX_ALL_AMOUNT, 
         A.trx_number                                           ORDER_BY, 
         LOC.address1                                           BILL_TO_ADDRESS1, 
         LOC.address2                                           BILL_TO_ADDRESS2, 
         LOC.address3                                           BILL_TO_ADDRESS3, 
         LOC.address4                                           BILL_TO_ADDRESS4, 
         LOC.state                                              BILL_TO_STATE, 
         LOC.province                                           BILL_TO_PROVINCE 
  FROM   ar_adjustments COM_ADJ, 
         ar_payment_schedules P, 
         ra_cust_trx_line_gl_dist REC, 
         ra_customer_trx A, 
         hz_cust_accounts B, 
         ra_terms T, 
         ra_terms_lines TL, 
         ra_cust_trx_types TYPES, 
         ar_lookups L_TYPES, 
         hz_parties PARTY, 
         hz_cust_acct_sites A_BILL, 
         hz_party_sites PARTY_SITE, 
         hz_locations LOC, 
         hz_cust_site_uses U_BILL 
  WHERE  A.bill_to_customer_id = B.cust_account_id 
         AND REC.customer_trx_id = A.customer_trx_id 
         AND REC.latest_rec_flag = 'Y' 
         AND REC.account_class = 'REC' 
         AND P.payment_schedule_id + Decode(P.class, 'INV', 0, '') = COM_ADJ.payment_schedule_id(+) 
         AND COM_ADJ.subsequent_trx_id IS NULL 
         AND 'C' = COM_ADJ.adjustment_type(+) 
         AND A.complete_flag = 'Y' 
         AND A.cust_trx_type_id = TYPES.cust_trx_type_id 
         AND L_TYPES.lookup_type = 'INV/CM/ADJ' 
         AND A.printing_option IN ( 'PRI', 'REP' ) 
         AND L_TYPES.lookup_code = Decode(TYPES.TYPE, 'DEP', 'INV', 
                                                      TYPES.TYPE) 
         AND Nvl(P.terms_sequence_number, Nvl(TL.sequence_num, 0)) = 
             Nvl(TL.sequence_num, Nvl(p.terms_sequence_number, 0)) 
         AND Decode(P.payment_schedule_id, '', 0, 
                                           Nvl(T.printing_lead_days, 0)) = 0 
         AND A.bill_to_site_use_id = U_BILL.site_use_id 
         AND U_BILL.cust_acct_site_id = A_BILL.cust_acct_site_id 
         AND A_BILL.party_site_id = party_site.party_site_id 
         AND B.party_id = PARTY.party_id 
         AND loc.location_id = party_site.location_id 
         AND Nvl(LOC.LANGUAGE, 'US') = 'US' 
         AND Nvl(P.amount_due_remaining, 0) &lt;&gt; 0 
         AND A.term_id = TL.term_id(+) 
         AND A.term_id = T.term_id(+) 
         AND A.customer_trx_id = P.customer_trx_id(+) 
         AND A.printing_pending = 'Y' 
         AND Nvl(TL.sequence_num, 1) &gt; Nvl(A.last_printed_sequence_num, 0) 
         AND Nvl(LOC.LANGUAGE, 'US') = 'US' 
         AND NOT EXISTS 
         (SELECT 'X' 
             FROM   ece_tp_details ETD, 
                    ece_tp_headers ETH 
             WHERE  ETH.tp_header_id = A_BILL.tp_header_id 
                    AND ETD.tp_header_id = ETH.tp_header_id 
                    AND ETD.edi_flag = 'Y' 
                    AND ETD.document_id = 'INO' 
                    AND ETD.document_type = Decode (TYPES.TYPE, 'CM',Decode(A.previous_customer_trx_id, NULL, 'OACM', 'CM'), TYPES.TYPE)) 
  UNION 
  SELECT a.customer_trx_id, 
         a.trx_number, 
         Nvl(P.terms_sequence_number, 1), 
         types.TYPE, 
         l_types.meaning, 
         TYPES.accounting_affect_flag, 
         a.trx_date, 
         A.ship_to_customer_id, 
         A.ship_to_contact_id, 
         A.remit_to_address_id, 
         A.primary_salesrep_id, 
         B.account_number, 
         A.internal_notes, 
         A.batch_source_id, 
         A.comments, 
         previous_customer_trx_id, 
         ship_to_site_use_id, 
         Nvl(printing_count, 0), 
         printing_original_date, 
         printing_last_printed, 
         printing_pending, 
         last_printed_sequence_num, 
         start_date_commitment, 
         end_date_commitment, 
         initial_customer_trx_id, 
         A.invoice_currency_code, 
         A.term_id, 
         A.ship_date_actual, 
         A.ship_via, 
         A.waybill_number, 
         A.purchase_order, 
         A.purchase_order_revision, 
         A.purchase_order_date, 
         P.due_date, 
         Nvl(TL.relative_amount, 100) * ( 100 / Nvl(T.base_amount, 100) ), 
         T.name, 
         A.bill_to_customer_id, 
         A.bill_to_contact_id, 
         A.bill_to_site_use_id, 
         U_BILL.location                                        BILL_TO_LOCATION, 
         Nvl(A_BILL.translated_customer_name, PARTY.party_name) BILL_CUST_NAME, 
         Rtrim(Rpad(LOC.address1, 40)), 
         Rtrim(Rpad(LOC.address2, 40)), 
         Rtrim(Rpad(LOC.address3, 40)), 
         Rtrim(Rpad(LOC.address4, 40)), 
         LOC.city, 
         Nvl(LOC.state, LOC.province), 
         LOC.postal_code, 
         LOC.country, 
         U_BILL.tax_reference, 
         PARTY.tax_reference, 
         Nvl(amount_line_items_original, To_number(NULL)), 
         Nvl(tax_original, To_number(NULL)), 
         Nvl(freight_original, To_number(NULL)), 
         p.amount_due_original, 
         A.trx_number, 
         LOC.address1, 
         LOC.address2, 
         LOC.address3, 
         LOC.address4, 
         LOC.state, 
         LOC.province 
  FROM   ra_terms_lines TL, 
         ra_cust_trx_types TYPES, 
         ar_lookups L_TYPES, 
         hz_cust_accounts B, 
         hz_parties PARTY, 
         hz_cust_site_uses U_BILL, 
         hz_cust_acct_sites A_BILL, 
         hz_party_sites PARTY_SITE, 
         hz_locations LOC, 
         ar_adjustments COM_ADJ, 
         ra_customer_trx A, 
         ar_payment_schedules P, 
         ra_terms T 
  WHERE  A.bill_to_customer_id = B.cust_account_id 
         AND P.payment_schedule_id 
             + Decode(P.class, 'INV', 0, '') = COM_ADJ.payment_schedule_id(+) 
         AND COM_ADJ.subsequent_trx_id IS NULL 
         AND 'C' = COM_ADJ.adjustment_type(+) 
         AND A.complete_flag = 'Y' 
         AND A.customer_trx_id = P.customer_trx_id 
         AND A.cust_trx_type_id = TYPES.cust_trx_type_id 
         AND L_TYPES.lookup_type = 'INV/CM/ADJ' 
         AND A.printing_option IN ( 'PRI', 'REP' ) 
         AND L_TYPES.lookup_code = Decode(TYPES.TYPE, 'DEP', 'INV',TYPES.TYPE) 
         AND Nvl(T.printing_lead_days, 0) &gt; 0 
         AND A.bill_to_site_use_id = U_BILL.site_use_id 
         AND U_BILL.cust_acct_site_id = A_BILL.cust_acct_site_id 
         AND A_BILL.party_site_id = PARTY_SITE.party_site_id 
         AND B.party_id = PARTY.party_id 
         AND LOC.location_id = PARTY_SITE.location_id 
         AND Nvl(LOC.LANGUAGE, 'US') = 'US' 
         AND Nvl(P.terms_sequence_number, TL.sequence_num) = TL.sequence_num 
         AND Nvl(P.amount_due_remaining, 0) &lt;&gt; 0 
         AND T.term_id = P.term_id 
         AND TL.term_id(+) = T.term_id 
         AND A.printing_pending = 'Y' 
         AND P.terms_sequence_number &gt; Nvl(A.last_printed_sequence_num, 0) 
         AND Nvl(LOC.LANGUAGE, 'US') = 'US' 
         AND NOT EXISTS 
         (SELECT 'X' 
             FROM   ece_tp_details ETD, 
                    ece_tp_headers ETH 
             WHERE  ETH.tp_header_id = A_BILL.tp_header_id 
                    AND ETD.tp_header_id = ETH.tp_header_id 
                    AND ETD.edi_flag = 'Y' 
                    AND ETD.document_id = 'INO' 
                    AND ETD.document_type = Decode (TYPES.TYPE, 'CM',Decode(A.previous_customer_trx_id,NULL, 'OACM','CM'),TYPES.TYPE))
  </code></pre>
  
  <p>Thank you!</p>