14 Replies Latest reply on Jun 9, 2017 3:14 PM by Srini Chavali-Oracle

    Invoice Print New Invoices program header query is taking long time for US org

    Rajesh123

      Hi All,

       

      I have customized standard Invoice Print New Invoices program rdf report source.

       

      Developed XML report.

       

      But the header select query is taking very long time to complete from backend/front end as well.

       

      This is only for only one organization (US Org), but not for other organizations.

       

      Can you please help me on this?

       

       

      Oracle Apps R12.1.3

        • 1. Re: Invoice Print New Invoices program header query is taking long time for US org
          Kanda-Oracle

          Hi,

           

          Just wanted to check whether this Org has huge data which could cause slowness. If thats the case, you may need to re-look at the query/DML and tune accordingly.


          Thanks!

          • 2. Re: Invoice Print New Invoices program header query is taking long time for US org
            Rajesh123

            Hi,

             

            Thanks for your quick response.

             

            The AR New Invoice Header select query is having only 111 rows for US org.

             

            But in the RA_CUST_TRX_LINE_GL_DIST_ALL table 214,120,783 rows for US org.

             

            Please let me know if more information required.

            • 3. Re: Invoice Print New Invoices program header query is taking long time for US org
              Kanda-Oracle

              Then you may need to re-write the query little different so that it fetches relatively faster .

              • 4. Re: Invoice Print New Invoices program header query is taking long time for US org
                Rajesh123

                The AR New Invoice header will generated by standard procedure(we have lexical's ).

                 

                Then you may need to re-write the query little different so that it fetches relatively faster .

                 

                I don't think, any idea on this?

                 

                SELECT a.customer_trx_id                                      CUSTOMER_TRX_ID,

                       a.trx_number                                           TRX_NUMBER

                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 T.term_id = P.term_id

                       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) > 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))

                       AND a.trx_date = a.trx_date

                UNION

                SELECT a.customer_trx_id,

                       a.trx_number

                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) > 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 T.term_id = P.term_id

                       AND TL.term_id = T.term_id

                       AND A.printing_pending = 'Y'

                       AND P.terms_sequence_number > 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))

                       AND a.trx_date = a.trx_date

                • 6. Re: Invoice Print New Invoices program header query is taking long time for US org
                  Rajesh123

                  Hi Srini,

                   

                  I will post Explain Plan soon.

                   

                  Are EBS statistics current ?

                   

                  Can u please explain little bit more?

                  • 7. Re: Invoice Print New Invoices program header query is taking long time for US org
                    Rajesh123

                    Hi ,

                    Here is the Explain Plan.

                     

                    Elapsed: 01:47:49.51

                    SQL> SELECT *

                      2  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

                     

                     

                    PLAN_TABLE_OUTPUT

                    ----------------------------------------------------------------------------------------------------

                    SQL_ID  8kdx252ypgt9p, child number 0

                    -------------------------------------

                    SELECT    /*+ GATHER_PLAN_STATISTICS */  a.customer_trx_id

                       CUSTOMER_TRX_ID ,    a.trx_number                       TRX_NUMBER

                    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

                     

                     

                    PLAN_TABLE_OUTPUT

                    ----------------------------------------------------------------------------------------------------

                    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_

                     

                     

                    Plan hash value: 596253025

                     

                     

                    ----------------------------------------------------------------------------------------------------

                    | Id  | Operation                                      | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads

                     

                     

                    PLAN_TABLE_OUTPUT

                    ---------------------------------------------------------------------------------------------------------------------------

                    |   0 | SELECT STATEMENT                               |                              |      1 |        |    111 |00:36:12.01 |    9767K|   1161K|       |

                    |   1 |  SORT UNIQUE                                   |                              |      1 |      2 |    111 |00:36:12.01 |    9767K|   1161K

                    |   2 |   UNION-ALL                                    |                              |      1 |        |    111 |00:00:04.74 |    9767K|   1161K|       |       |     

                    |*  3 |    FILTER                                      |                              |      1 |        |    111 |00:00:04.74 |    1029K|    433 |       |       |     

                    |   4 |     NESTED LOOPS OUTER                         |                              |      1 |      1 |    111 |00:00:04.74 |    1029K

                    |   5 |      NESTED LOOPS ANTI                         |                              |      1 |      1 |    111 |00:00:04.74 |    1029K

                    |   6 |       NESTED LOOPS                             |                              |      1 |      1 |    111 |00:00:04.74 |    1029K|  

                    |   7 |        NESTED LOOPS                            |                              |      1 |      1 |    111 |00:00:04.74 |    1029K| 

                    |   8 |         NESTED LOOPS                           |                              |      1 |      1 |    111 |00:00:04.74 |    1028K|    316

                    |   9 |          NESTED LOOPS                          |                              |      1 |      1 |    111 |00:00:04.74 |    1028K|    236

                     

                     

                    PLAN_TABLE_OUTPUT

                    ------------------------------------------------------------------------------------------------------------------------------

                    |  10 |           NESTED LOOPS                         |                              |      1 |      1 |    111 |00:00:04.74 |    1027K|    23

                    |  11 |            NESTED LOOPS                        |                              |      1 |      1 |    111 |00:00:04.74 |    1027K|    2

                    |  12 |             NESTED LOOPS                       |                              |      1 |      1 |    111 |00:00:04.74 |    1027K|    2

                    |  13 |              NESTED LOOPS                      |                              |      1 |      1 |    111 |00:00:04.73 |    1026K|   

                    |* 14 |               FILTER                           |                              |      1 |        |    476K|00:00:03.14 |     341K|     58 |       |       |

                    |  15 |                NESTED LOOPS OUTER              |                              |      1 |      1 |    476K|00:00:02.98 |     34

                    |  16 |                 NESTED LOOPS OUTER             |                              |      1 |      1 |    471K|00:00:02.68 |     341K|  

                    |  17 |                  NESTED LOOPS                  |                              |      1 |      1 |    471K|00:00:01.00 |     137K|     58

                    |* 18 |                   HASH JOIN                    |                              |      1 |      1 |   1121 |00:00:00.08 |     280 |     39 |

                    |* 19 |                    INDEX RANGE SCAN            | FND_LOOKUP_VALUES_U1         |      1 |      3 |      9 |00:00:

                    |  20 |                    TABLE ACCESS BY INDEX ROWID | RA_CUST_TRX_TYPES_ALL        |      1 |    136 |   1121 |0

                     

                     

                    PLAN_TABLE_OUTPUT

                    --------------------------------------------------------------------------------------------------------------------------------

                    |* 21 |                     INDEX SKIP SCAN            | RA_CUST_TRX_TYPES_U1         |      1 |    136 |   1121 |00:00:

                    |  22 |                   INLIST ITERATOR              |                              |   1121 |        |    471K|00:00:00.98 |     137K|     19 |      

                    |* 23 |                    TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL          |   2242 |     32 |    471K|00:

                    |* 24 |                     INDEX RANGE SCAN           | FLT_RA_CUSTOMER_TRX_N32      |   2242 |    253 |    471K|00:

                    |  25 |                  VIEW PUSHED PREDICATE         | RA_TERMS_VL                  |    471K|      1 |    204K|00:00:01.29

                    |  26 |                   NESTED LOOPS OUTER           |                              |    471K|      1 |    204K|00:00:01.08 |     204K|

                    |  27 |                    NESTED LOOPS                |                              |    471K|      1 |    204K|00:00:00.94 |     204K|    

                    |  28 |                     TABLE ACCESS BY INDEX ROWID| RA_TERMS_B                   |    471K|      1 |    204K|00:00:00.57

                    |* 29 |                      INDEX UNIQUE SCAN         | RA_TERMS_B_U1                |    471K|      1 |    204K|00:00:00.

                    |* 30 |                     INDEX UNIQUE SCAN          | RA_TERMS_TL_U1               |    204K|      1 |    204K|00:00:00.

                    |* 31 |                    INDEX UNIQUE SCAN           | AR_CONS_BILL_CYCLES_TL_U1    |    204K|      1 |      0 |00:

                     

                     

                    PLAN_TABLE_OUTPUT

                    -------------------------------------------------------------------------------------------------------------------------------

                    |* 32 |                 INDEX RANGE SCAN               | RA_TERMS_LINES_U1            |    471K|      2 |    209K|00:00:00.25

                    |* 33 |               TABLE ACCESS BY INDEX ROWID      | AR_PAYMENT_SCHEDULES_ALL     |    476K|      1 | 

                    |* 34 |                INDEX RANGE SCAN                | AR_PAYMENT_SCHEDULES_N2      |    476K|      3 |    501K|

                    |* 35 |              TABLE ACCESS BY INDEX ROWID       | RA_CUST_TRX_LINE_GL_DIST_ALL |    111 |      1 | 

                    |* 36 |               INDEX RANGE SCAN                 | RA_CUST_TRX_LINE_GL_DIST_N6  |    111 |     47 |    111 |0

                    |* 37 |             TABLE ACCESS BY INDEX ROWID        | HZ_CUST_SITE_USES_ALL        |    111 |      1 |  

                    |* 38 |              INDEX UNIQUE SCAN                 | HZ_CUST_SITE_USES_U1         |    111 |      1 |    111 |00:

                    |* 39 |            TABLE ACCESS BY INDEX ROWID         | HZ_CUST_ACCT_SITES_ALL       |    111 |      1 |  

                    |* 40 |             INDEX UNIQUE SCAN                  | HZ_CUST_ACCT_SITES_U1        |    111 |      1 |    111 |00:

                    |  41 |           TABLE ACCESS BY INDEX ROWID          | HZ_CUST_ACCOUNTS             |    111 |      1 |    111 |0

                    |* 42 |            INDEX UNIQUE SCAN                   | HZ_CUST_ACCOUNTS_U1          |    111 |      1 |    111 |00:00:

                     

                     

                    PLAN_TABLE_OUTPUT

                    ---------------------------------------------------------------------------------------------------------------------------------

                    |  43 |          TABLE ACCESS BY INDEX ROWID           | HZ_PARTY_SITES               |    111 |      1 |    111 |00:

                    |* 44 |           INDEX UNIQUE SCAN                    | HZ_PARTY_SITES_U1            |    111 |      1 |    111 |00:00:00.

                    |* 45 |         TABLE ACCESS BY INDEX ROWID            | HZ_LOCATIONS                 |    111 |      1 |    111 |00:00:

                    |* 46 |          INDEX UNIQUE SCAN                     | HZ_LOCATIONS_U1              |    111 |      1 |    111 |00:00:00.18

                    |* 47 |        INDEX UNIQUE SCAN                       | HZ_PARTIES_U1                |    111 |      1 |    111 |00:00:00

                    |  48 |       VIEW PUSHED PREDICATE                    | VW_SQ_1                      |    111 |      1 |      0 |00:00:00.01

                    |  49 |        NESTED LOOPS                            |                              |    111 |      1 |      0 |00:00:00.01 |       0 | 

                    |* 50 |         INDEX UNIQUE SCAN                      | ECE_TP_HEADERS_U1            |    111 |      1 |      0 |00:00:00.01

                    |* 51 |         TABLE ACCESS BY INDEX ROWID            | ECE_TP_DETAILS               |      0 |      1 |      0 |00:0

                    |* 52 |          INDEX UNIQUE SCAN                     | ECE_TP_DETAILS_U2            |      0 |      1 |      0 |00:00:00.0

                    |* 53 |      TABLE ACCESS BY INDEX ROWID               | AR_ADJUSTMENTS_ALL           |    111 |      1 |     

                     

                     

                    PLAN_TABLE_OUTPUT

                    -----------------------------------------------------------------------------------------------------------------------------------

                    |* 54 |       INDEX RANGE SCAN                         | AR_ADJUSTMENTS_N3            |    111 |      2 |      0 |00:00:

                    |* 55 |    FILTER                                      |                              |      1 |        |      0 |00:36:04.46 |    8738K|   1161K|       |       |     

                    |* 56 |     FILTER                                     |                              |      1 |        |      0 |00:36:04.46 |    8738K|   1161K|       |       |     

                    |  57 |      NESTED LOOPS OUTER                        |                              |      1 |      1 |      0 |00:36:04.46 |    8738

                    |  58 |       NESTED LOOPS                             |                              |      1 |      1 |      0 |00:36:04.46 |    8738K|  

                    |  59 |        NESTED LOOPS                            |                              |      1 |      1 |      0 |00:36:04.46 |    8738K| 

                    |  60 |         NESTED LOOPS OUTER                     |                              |      1 |      1 |      0 |00:36:04.46 |    8738K|  

                    |  61 |          NESTED LOOPS                          |                              |      1 |      1 |      0 |00:36:04.46 |    8738K|   1161

                    |  62 |           NESTED LOOPS                         |                              |      1 |      1 |    111 |00:03:55.26 |    8738K|   116

                    |  63 |            NESTED LOOPS                        |                              |      1 |      1 |    471K|01:46:15.70 |    8064K|   11

                    |  64 |             NESTED LOOPS                       |                              |      1 |      1 |    471K|01:42:30.05 |    7123K|   11

                     

                     

                    PLAN_TABLE_OUTPUT

                    ------------------------------------------------------------------------------------------------------------------------------------

                    |  65 |              NESTED LOOPS                      |                              |      1 |      1 |    471K|01:27:14.79 |    5714K|   

                    |  66 |               NESTED LOOPS                     |                              |      1 |      1 |    471K|01:03:02.22 |    4302K|  

                    |  67 |                NESTED LOOPS                    |                              |      1 |      1 |    471K|00:43:12.53 |    2891K| 

                    |  68 |                 NESTED LOOPS                   |                              |      1 |      1 |    471K|00:24:32.56 |    1515K|    264K

                    |  69 |                  NESTED LOOPS                  |                              |      1 |      1 |    471K|00:01:22.50 |     137K|  19400

                    |* 70 |                   HASH JOIN                    |                              |      1 |      1 |   1121 |00:00:00.04 |     280 |     61 |

                    |* 71 |                    INDEX RANGE SCAN            | FND_LOOKUP_VALUES_U1         |      1 |      3 |      9 |00:00:

                    |  72 |                    TABLE ACCESS BY INDEX ROWID | RA_CUST_TRX_TYPES_ALL        |      1 |    136 |   1121 |0

                    |* 73 |                     INDEX SKIP SCAN            | RA_CUST_TRX_TYPES_U1         |      1 |    136 |   1121 |00:00:

                    |  74 |                   INLIST ITERATOR              |                              |   1121 |        |    471K|00:02:45.97 |     137K|  19339 |      

                    |* 75 |                    TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL          |   2242 |     32 |    471K|00:

                     

                     

                    PLAN_TABLE_OUTPUT

                    -------------------------------------------------------------------------------------------------------------------------------------

                    |* 76 |                     INDEX RANGE SCAN           | FLT_RA_CUSTOMER_TRX_N32      |   2242 |    253 |    471K|00:

                    |* 77 |                  TABLE ACCESS BY INDEX ROWID   | HZ_CUST_SITE_USES_ALL        |    471K|      1 |    471K|0

                    |* 78 |                   INDEX UNIQUE SCAN            | HZ_CUST_SITE_USES_U1         |    471K|      1 |    471K|00:01:

                    |* 79 |                 TABLE ACCESS BY INDEX ROWID    | HZ_CUST_ACCT_SITES_ALL       |    471K|      1 |    471K|

                    |* 80 |                  INDEX UNIQUE SCAN             | HZ_CUST_ACCT_SITES_U1        |    471K|      1 |    471K|00:01:

                    |  81 |                TABLE ACCESS BY INDEX ROWID     | HZ_PARTY_SITES               |    471K|      1 |    471K|

                    |* 82 |                 INDEX UNIQUE SCAN              | HZ_PARTY_SITES_U1            |    471K|      1 |    471K|00:01:08.61

                    |* 83 |               TABLE ACCESS BY INDEX ROWID      | HZ_LOCATIONS                 |    471K|      1 |    471K|00

                    |* 84 |                INDEX UNIQUE SCAN               | HZ_LOCATIONS_U1              |    471K|      1 |    471K|00:01:0

                    |  85 |              TABLE ACCESS BY INDEX ROWID       | HZ_CUST_ACCOUNTS             |    471K|      1 |    471K

                    |* 86 |               INDEX UNIQUE SCAN                | HZ_CUST_ACCOUNTS_U1          |    471K|      1 |    471K|00:

                     

                     

                    PLAN_TABLE_OUTPUT

                    ---------------------------------------------------------------------------------------------------------------------------------------

                    |* 87 |             INDEX UNIQUE SCAN                  | HZ_PARTIES_U1                |    471K|      1 |    471K|00:03:41.4

                    |* 88 |            TABLE ACCESS BY INDEX ROWID         | AR_PAYMENT_SCHEDULES_ALL     |    471K|      1 |  

                    |* 89 |             INDEX RANGE SCAN                   | AR_PAYMENT_SCHEDULES_N2      |    471K|      3 |    476K|00:

                    |* 90 |           TABLE ACCESS BY INDEX ROWID          | RA_TERMS_B                   |    111 |      1 |      0 |00:00:

                    |* 91 |            INDEX UNIQUE SCAN                   | RA_TERMS_B_U1                |    111 |      1 |    111 |00:00:00.01

                    |* 92 |          INDEX UNIQUE SCAN                     | AR_CONS_BILL_CYCLES_TL_U1    |      0 |      1 |      0 |00:00

                    |* 93 |         INDEX UNIQUE SCAN                      | RA_TERMS_TL_U1               |      0 |      1 |      0 |00:00:00.01 |

                    |* 94 |        INDEX UNIQUE SCAN                       | RA_TERMS_LINES_U1            |      0 |      1 |      0 |00:00

                    |* 95 |       TABLE ACCESS BY INDEX ROWID              | AR_ADJUSTMENTS_ALL           |      0 |      1 |    

                    |* 96 |        INDEX RANGE SCAN                        | AR_ADJUSTMENTS_N3            |      0 |      2 |      0 |00:00

                    |  97 |     NESTED LOOPS                               |                              |      0 |      1 |      0 |00:00:00.01 |       0 |    

                     

                     

                    PLAN_TABLE_OUTPUT

                    ----------------------------------------------------------------------------------------------------------------------------------------

                    |* 98 |      INDEX UNIQUE SCAN                         | ECE_TP_HEADERS_U1            |      0 |      1 |      0 |00:00:

                    |* 99 |      TABLE ACCESS BY INDEX ROWID               | ECE_TP_DETAILS               |      0 |      1 |      0 |

                    |*100 |       INDEX UNIQUE SCAN                        | ECE_TP_DETAILS_U2            |      0 |      1 |      0 |00:00

                    ----------------------------------------------------------------------------------------------------

                     

                     

                    Predicate Information (identified by operation id):

                    ---------------------------------------------------

                     

                     

                       3 - filter("SUBSEQUENT_TRX_ID" IS NULL)

                      14 - filter(NVL("TL"."SEQUENCE_NUM",1)>NVL("LAST_PRINTED_SEQUENCE_NUM",0))

                      18 - access("LV"."LOOKUP_CODE"=DECODE("TYPE",'DEP','INV',"TYPE"))

                     

                     

                    PLAN_TABLE_OUTPUT

                    ----------------------------------------------------------------------------------------------------

                      19 - access("LV"."LOOKUP_TYPE"='INV/CM/ADJ' AND "LV"."VIEW_APPLICATION_ID"=222 AND "LV"."SECURITY_

                           filter(("LV"."LANGUAGE"=USERENV('LANG') AND "LV"."SECURITY_GROUP_ID"=0))

                      21 - access("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))

                           filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))

                      23 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))

                      24 - access("CUST_TRX_TYPE_ID"="CUST_TRX_TYPE_ID" AND "PRINTING_PENDING"='Y' AND "COMPLETE_FLAG"='

                      29 - access("B"."TERM_ID"="TERM_ID")

                      30 - access("T"."TERM_ID"="TERM_ID" AND "T"."LANGUAGE"=USERENV('LANG'))

                      31 - access("B"."BILLING_CYCLE_ID"="BC"."BILLING_CYCLE_ID" AND "BC"."LANGUAGE"=USERENV('LANG'))

                      32 - access("TERM_ID"="TL"."TERM_ID")

                      33 - filter(("STATUS"='OP' AND INTERNAL_FUNCTION("ORG_ID") AND "AMOUNT_DUE_REMAINING"<>0 AND

                     

                     

                    PLAN_TABLE_OUTPUT

                    ----------------------------------------------------------------------------------------------------

                                  NVL("TERMS_SEQUENCE_NUMBER",NVL("TL"."SEQUENCE_NUM",0))=NVL("TL"."SEQUENCE_NUM",NVL("TERMS_SE

                                  DECODE(TO_CHAR("PAYMENT_SCHEDULE_ID"),'',0,NVL("T"."PRINTING_LEAD_DAYS",0))=0))

                      34 - access("CUSTOMER_TRX_ID"="CUSTOMER_TRX_ID")

                           filter("CUSTOMER_TRX_ID" IS NOT NULL)

                      35 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))

                      36 - access("CUSTOMER_TRX_ID"="CUSTOMER_TRX_ID" AND "ACCOUNT_CLASS"='REC' AND "LATEST_REC_FLAG"='Y

                      37 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))

                      38 - access("BILL_TO_SITE_USE_ID"="SITE_USE_ID")

                      39 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))

                      40 - access("CUST_ACCT_SITE_ID"="CUST_ACCT_SITE_ID")

                      42 - access("BILL_TO_CUSTOMER_ID"="B"."CUST_ACCOUNT_ID")

                     

                     

                    PLAN_TABLE_OUTPUT

                    ----------------------------------------------------------------------------------------------------

                      44 - access("PARTY_SITE_ID"="PARTY_SITE"."PARTY_SITE_ID")

                      45 - filter(NVL("LOC"."LANGUAGE",'US')='US')

                      46 - access("LOC"."LOCATION_ID"="PARTY_SITE"."LOCATION_ID")

                      47 - access("B"."PARTY_ID"="PARTY"."PARTY_ID")

                      50 - access("ETH"."TP_HEADER_ID"="TP_HEADER_ID")

                      51 - filter("ETD"."EDI_FLAG"='Y')

                      52 - access("ETD"."TP_HEADER_ID"="TP_HEADER_ID" AND "ETD"."DOCUMENT_ID"='INO' AND

                                  "ETD"."DOCUMENT_TYPE"=DECODE("TYPE",'CM',DECODE(TO_CHAR("PREVIOUS_CUSTOMER_TRX_ID"),NULL,'OAC

                      53 - filter(("ADJUSTMENT_TYPE"='C' AND "ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id

                      54 - access("PAYMENT_SCHEDULE_ID"="PAYMENT_SCHEDULE_ID"+DECODE("CLASS",'INV',0,NULL))

                      55 - filter( IS NULL)

                     

                     

                    PLAN_TABLE_OUTPUT

                    ----------------------------------------------------------------------------------------------------

                      56 - filter("SUBSEQUENT_TRX_ID" IS NULL)

                      70 - access("LV"."LOOKUP_CODE"=DECODE("TYPE",'DEP','INV',"TYPE"))

                      71 - access("LV"."LOOKUP_TYPE"='INV/CM/ADJ' AND "LV"."VIEW_APPLICATION_ID"=222 AND "LV"."SECURITY_

                           filter(("LV"."LANGUAGE"=USERENV('LANG') AND "LV"."SECURITY_GROUP_ID"=0))

                      73 - access("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))

                           filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))

                      75 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))

                      76 - access("CUST_TRX_TYPE_ID"="CUST_TRX_TYPE_ID" AND "PRINTING_PENDING"='Y' AND "COMPLETE_FLAG"='

                      77 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))

                      78 - access("BILL_TO_SITE_USE_ID"="SITE_USE_ID")

                      79 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')))

                     

                     

                    PLAN_TABLE_OUTPUT

                    ----------------------------------------------------------------------------------------------------

                      80 - access("CUST_ACCT_SITE_ID"="CUST_ACCT_SITE_ID")

                      82 - access("PARTY_SITE_ID"="PARTY_SITE"."PARTY_SITE_ID")

                      83 - filter(NVL("LOC"."LANGUAGE",'US')='US')

                      84 - access("LOC"."LOCATION_ID"="PARTY_SITE"."LOCATION_ID")

                      86 - access("BILL_TO_CUSTOMER_ID"="B"."CUST_ACCOUNT_ID")

                      87 - access("B"."PARTY_ID"="PARTY"."PARTY_ID")

                      88 - filter(("TERM_ID" IS NOT NULL AND "STATUS"='OP' AND INTERNAL_FUNCTION("ORG_ID") AND "AMOUNT_D

                                  "TERMS_SEQUENCE_NUMBER">NVL("LAST_PRINTED_SEQUENCE_NUM",0)))

                      89 - access("CUSTOMER_TRX_ID"="CUSTOMER_TRX_ID")

                           filter("CUSTOMER_TRX_ID" IS NOT NULL)

                      90 - filter(NVL("B"."PRINTING_LEAD_DAYS",0)>0)

                     

                     

                    PLAN_TABLE_OUTPUT

                    ----------------------------------------------------------------------------------------------------

                      91 - access("B"."TERM_ID"="TERM_ID")

                      92 - access("B"."BILLING_CYCLE_ID"="BC"."BILLING_CYCLE_ID" AND "BC"."LANGUAGE"=USERENV('LANG'))

                      93 - access("B"."TERM_ID"="T"."TERM_ID" AND "T"."LANGUAGE"=USERENV('LANG'))

                      94 - access("TL"."TERM_ID"="B"."TERM_ID" AND "TL"."SEQUENCE_NUM"="TERMS_SEQUENCE_NUMBER")

                      95 - filter(("ADJUSTMENT_TYPE"='C' AND "ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id

                      96 - access("PAYMENT_SCHEDULE_ID"="PAYMENT_SCHEDULE_ID"+DECODE("CLASS",'INV',0,NULL))

                      98 - access("ETH"."TP_HEADER_ID"=:B1)

                      99 - filter("ETD"."EDI_FLAG"='Y')

                    100 - access("ETD"."TP_HEADER_ID"=:B1 AND "ETD"."DOCUMENT_ID"='INO' AND "ETD"."DOCUMENT_TYPE"=DECOD

                     

                     

                     

                     

                    197 rows selected.

                    • 8. Re: Invoice Print New Invoices program header query is taking long time for US org
                      Kanda-Oracle

                      He meant,  Did you run 'Gather Schema Statistics' recently.

                      • 11. Re: Invoice Print New Invoices program header query is taking long time for US org
                        Rajesh123

                        Hi Srini,

                        The 2nd union query is taking very long time to complete.

                        • 12. Re: Invoice Print New Invoices program header query is taking long time for US org
                          Rajesh123

                          Hi Experts , can you please suggest on this?

                           

                          Thanks in advanced.

                          • 13. Re: Invoice Print New Invoices program header query is taking long time for US org
                            Rajesh123

                            Hi All,

                             

                            Does this query will pick up OP status (payment schedule) Invoices at run time?