1 2 Previous Next 21 Replies Latest reply: Dec 24, 2013 12:35 AM by Karan Kukreja RSS

    Need understanding on the ADDM

    Karan Kukreja

      Hi,

       

       

      Db version 11.2.0.3

      EBS version 12.1.3

      OS : Linux

       

       

      I have 2 SEV-1 SR's going on with Oracle on which we are working with them. I ran a ADDM from yesterday Morning till today afternoon( Almost 25 Hours).

       

       

      My understanding :

      1. Update query 85ag5gkm9wzmb is taking maximum of time and resources ( One SR is for this - this is Autoinvoice Master Program).

      2. SGA and PGA are undersized. Current SGA is 24Gb and PGA is 6Gb. Do I really need to increase SGA and PGA ? My previous has been that on increasing

      SGA/PGA to suggested value , it will further prompt for increasing.

      3. SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC needs some action from my end, should I disable this job , what does it really do ? i am trying to figure that out ,

      any help on this will be appreciated.

       

      Please confirm if my understanding is correct and other suggestion/inputs you can provide. Also please suggest how to find if I/O is a bottleneck in the system ? I can see db file sequential read and db file scattered reads in the AWR but they are mostly by the update statement 85ag5gkm9wzmb.

       

       

      Below is the ADDM for reference.

       

      ***********************************************************************************************************************************************************

       

                ADDM Report for Task 'TASK_10569'

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

       

      Analysis Period

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

      AWR snapshot range from 6708 to 6733.

      Time period starts at 19-DEC-13 11.30.37 AM

      Time period ends at 20-DEC-13 12.30.43 PM

       

      Analysis Target

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

      Database 'PROD' with DB ID 221722471.

      Database version 11.2.0.3.0.

      ADDM performed an analysis of instance PROD, numbered 1 and hosted at

      hostname.domainname.in.

       

      Activity During the Analysis Period

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

      Total database time was 448593 seconds.

      The average number of active sessions was 4.98.

       

      Summary of Findings

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

         Description                               Active Sessions      Recommendations

                                                   Percent of Activity  

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

      1  Top SQL Statements                        3.44 | 69.1          6

      2  Top Segments by "User I/O" and "Cluster"  2.64 | 53.06         5

      3  Undersized SGA                            .94 | 18.91          1

      4  Undersized PGA                            .28 | 5.66           0

      5  PL/SQL Execution                          .19 | 3.9            5

       

       

      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

       

       

                Findings and Recommendations

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

       

      Finding 1: Top SQL Statements

      Impact is 3.44 active sessions, 69.1% of total activity.

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

      SQL statements consuming significant database time were found. These

      statements offer a good opportunity for performance improvement.

       

         Recommendation 1: SQL Tuning

         Estimated benefit is 2.26 active sessions, 45.37% of total activity.

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

         Action

            Run SQL Tuning Advisor on the UPDATE statement with SQL_ID

            "85ag5gkm9wzmb".

            Related Object

               SQL statement with SQL_ID 85ag5gkm9wzmb.

               UPDATE RA_CUST_TRX_LINE_GL_DIST LGD SET (AMOUNT, ACCTD_AMOUNT) =

               (SELECT /*+ index(rec1 RA_CUST_TRX_LINE_GL_DIST_N6) ordered */

               NVL(LGD.AMOUNT, 0) - ( SUM(LGD2.AMOUNT) - ( DECODE(LGD.GL_DATE,

               REC1.GL_DATE, 1, 0) * CTL.EXTENDED_AMOUNT ) ), NVL(LGD.ACCTD_AMOUNT,

               0) - ( SUM(LGD2.ACCTD_AMOUNT) - ( DECODE(LGD.GL_DATE, REC1.GL_DATE,

               1, 0) * DECODE(:B2 , NULL, ROUND( CTL.EXTENDED_AMOUNT *

               NVL(CT.EXCHANGE_RATE,1), :B3 ), ROUND( ( CTL.EXTENDED_AMOUNT *

               NVL(CT.EXCHANGE_RATE,1) ) / :B2 ) * :B2 ) ) ) FROM

               RA_CUSTOMER_TRX_LINES CTL, RA_CUSTOMER_TRX CT,

               RA_CUST_TRX_LINE_GL_DIST LGD2, RA_CUST_TRX_LINE_GL_DIST REC1 WHERE

               CTL.CUSTOMER_TRX_LINE_ID = LGD2.CUSTOMER_TRX_LINE_ID AND

               CTL.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND LGD.CUSTOMER_TRX_LINE_ID

               = CTL.CUSTOMER_TRX_LINE_ID AND LGD2.ACCOUNT_SET_FLAG = 'N' AND

               REC1.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND REC1.ACCOUNT_CLASS =

               'REC' AND REC1.LATEST_REC_FLAG = 'Y' AND NVL(LGD.GL_DATE, TO_DATE(

               2415021, 'J') ) = NVL(LGD2.GL_DATE, TO_DATE( 2415021, 'J') ) GROUP BY

               CTL.CUSTOMER_TRX_LINE_ID, REC1.GL_DATE, CTL.EXTENDED_AMOUNT,

               CTL.REVENUE_AMOUNT, CT.EXCHANGE_RATE ), PERCENT = (SELECT /*+

               index(rec2 RA_CUST_TRX_LINE_GL_DIST_N6) */ DECODE(LGD.ACCOUNT_CLASS

               || LGD.ACCOUNT_SET_FLAG, 'SUSPENSEN', LGD.PERCENT, 'UNBILLN',

               LGD.PERCENT, 'UNEARNN', LGD.PERCENT, NVL(LGD.PERCENT, 0) - (

               SUM(NVL(LGD4.PERCENT, 0)) - DECODE(REC2.GL_DATE, NVL(LGD.GL_DATE,

               REC2.GL_DATE), 100, 0) ) ) FROM RA_CUST_TRX_LINE_GL_DIST LGD4,

               RA_CUST_TRX_LINE_GL_DIST REC2 WHERE LGD.CUSTOMER_TRX_LINE_ID =

               LGD4.CUSTOMER_TRX_LINE_ID AND REC2.CUSTOMER_TRX_ID =

               LGD.CUSTOMER_TRX_ID AND REC2.CUSTOMER_TRX_ID = LGD4.CUSTOMER_TRX_ID

               AND REC2.ACCOUNT_CLASS = 'REC' AND REC2.LATEST_REC_FLAG = 'Y' AND

               LGD4.ACCOUNT_SET_FLAG = LGD.ACCOUNT_SET_FLAG AND

               DECODE(LGD4.ACCOUNT_SET_FLAG, 'Y', LGD4.ACCOUNT_CLASS,

               LGD.ACCOUNT_CLASS) = LGD.ACCOUNT_CLASS AND NVL(LGD.GL_DATE, TO_DATE(

               2415021, 'J') ) = NVL(LGD4.GL_DATE, TO_DATE( 2415021, 'J') ) GROUP BY

               REC2.GL_DATE, LGD.GL_DATE ), LAST_UPDATED_BY = :B1 , LAST_UPDATE_DATE

               = SYSDATE WHERE CUST_TRX_LINE_GL_DIST_ID IN (SELECT /*+ index(rec3

               RA_CUST_TRX_LINE_GL_DIST_N6) */ MIN(DECODE(LGD3.GL_POSTED_DATE, NULL,

               LGD3.CUST_TRX_LINE_GL_DIST_ID, NULL) ) FROM RA_CUSTOMER_TRX_LINES

               CTL, RA_CUSTOMER_TRX T, RA_CUST_TRX_LINE_GL_DIST LGD3,

               RA_CUST_TRX_LINE_GL_DIST REC3 WHERE T.REQUEST_ID = :B5 AND

               T.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID AND (CTL.LINE_TYPE IN (

               'TAX','FREIGHT','CHARGES','SUSPENSE' ) OR (CTL.LINE_TYPE = 'LINE' AND

               CTL.ACCOUNTING_RULE_ID IS NULL )) AND LGD3.CUSTOMER_TRX_LINE_ID =

               CTL.CUSTOMER_TRX_LINE_ID AND LGD3.ACCOUNT_SET_FLAG = 'N' AND

               REC3.CUSTOMER_TRX_ID = T.CUSTOMER_TRX_ID AND REC3.ACCOUNT_CLASS =

               'REC' AND REC3.LATEST_REC_FLAG = 'Y' AND

               NVL(T.PREVIOUS_CUSTOMER_TRX_ID, -1) = DECODE(:B4 , 'INV', -1,

               'REGULAR_CM', T.PREVIOUS_CUSTOMER_TRX_ID,

               NVL(T.PREVIOUS_CUSTOMER_TRX_ID, -1) ) GROUP BY

               CTL.CUSTOMER_TRX_LINE_ID, LGD3.GL_DATE, REC3.GL_DATE,

               CTL.EXTENDED_AMOUNT, CTL.REVENUE_AMOUNT, T.EXCHANGE_RATE HAVING (

               SUM(NVL(LGD3.AMOUNT, 0)) <> CTL.EXTENDED_AMOUNT *

               DECODE(LGD3.GL_DATE, REC3.GL_DATE, 1, 0) OR

               SUM(NVL(LGD3.ACCTD_AMOUNT, 0)) <> DECODE(LGD3.GL_DATE, REC3.GL_DATE,

               1, 0) * DECODE(:B2 , NULL, ROUND( CTL.EXTENDED_AMOUNT *

               NVL(T.EXCHANGE_RATE,1), :B3 ), ROUND( ( CTL.EXTENDED_AMOUNT *

               NVL(T.EXCHANGE_RATE,1) ) / :B2 ) * :B2 ) ) UNION SELECT /*+

               index(rec5 RA_CUST_TRX_LINE_GL_DIST_N6) INDEX (lgd5

               ra_cust_trx_line_gl_dist_n6) index(ctl2 ra_customer_trx_lines_u1) */

               TO_NUMBER( MIN(DECODE(LGD5.GL_POSTED_DATE||LGD5.ACCOUNT_CLASS||

               LGD5.ACCOUNT_SET_FLAG, 'REVN', LGD5.CUST_TRX_LINE_GL_DIST_ID, 'REVY',

               LGD5.CUST_TRX_LINE_GL_DIST_ID, 'TAXN', LGD5.CUST_TRX_LINE_GL_DIST_ID,

               'TAXY', LGD5.CUST_TRX_LINE_GL_DIST_ID, 'FREIGHTN',

               LGD5.CUST_TRX_LINE_GL_DIST_ID, 'FREIGHTY',

               LGD5.CUST_TRX_LINE_GL_DIST_ID, 'CHARGESN',

               LGD5.CUST_TRX_LINE_GL_DIST_ID, 'CHARGESY',

               LGD5.CUST_TRX_LINE_GL_DIST_ID, 'UNEARNY',

               LGD5.CUST_TRX_LINE_GL_DIST_ID, 'UNBILLY',

               LGD5.CUST_TRX_LINE_GL_DIST_ID, NULL ) ) ) FROM

               RA_CUST_TRX_LINE_GL_DIST LGD5, RA_CUST_TRX_LINE_GL_DIST REC5,

               RA_CUSTOMER_TRX_LINES CTL2, RA_CUSTOMER_TRX T WHERE T.REQUEST_ID =

               :B5 AND T.CUSTOMER_TRX_ID = REC5.CUSTOMER_TRX_ID AND

               CTL2.CUSTOMER_TRX_LINE_ID = LGD5.CUSTOMER_TRX_LINE_ID AND

               REC5.CUSTOMER_TRX_ID = LGD5.CUSTOMER_TRX_ID AND REC5.ACCOUNT_CLASS =

               'REC' AND REC5.LATEST_REC_FLAG = 'Y' AND (CTL2.LINE_TYPE IN (

               'TAX','FREIGHT','CHARGES','SUSPENSE') OR (CTL2.LINE_TYPE = 'LINE' AND

               (CTL2.ACCOUNTING_RULE_ID IS NULL OR LGD5.ACCOUNT_SET_FLAG = 'Y' )))

               GROUP BY LGD5.CUSTOMER_TRX_LINE_ID, LGD5.GL_DATE, REC5.GL_DATE,

               LGD5.ACCOUNT_SET_FLAG, DECODE(LGD5.ACCOUNT_SET_FLAG, 'N', NULL,

               LGD5.ACCOUNT_CLASS) HAVING SUM(NVL(LGD5.PERCENT, 0)) <> DECODE(

               NVL(LGD5.GL_DATE, REC5.GL_DATE), REC5.GL_DATE, 100, 0) )

         Rationale

            The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

            This part of database time may be improved by the SQL Tuning Advisor.

         Rationale

            Database time for this SQL was divided as follows: 100% for SQL

            execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

            execution.

         Rationale

            SQL statement with SQL_ID "85ag5gkm9wzmb" was executed 20 times and had

            an average elapsed time of 9763 seconds.

         Rationale

            Top level calls to execute the PL/SQL statement with SQL_ID

            "gccbpwamzp78q" are responsible for 100% of the database time spent on

            the UPDATE statement with SQL_ID "85ag5gkm9wzmb".

            Related Object

               SQL statement with SQL_ID gccbpwamzp78q.

               declare l_header_rounding_flag VARCHAR2 ( 1 ) ; BEGIN select

               trx_header_level_rounding into l_header_rounding_flag from

               ar_system_parameters ; :return_value:i_return_value := arp_rounding .

               correct_dist_rounding_errors ( :request_id , NULL , NULL ,

               :rows_processed:i_rows_processed , :error_msg:i_error_msg , NULL ,

               NULL , :trx_class , 'N' , :debug_mode , l_header_rounding_flag , 'N'

               ) ; END ;

       

         Recommendation 2: SQL Tuning

         Estimated benefit is .42 active sessions, 8.39% of total activity.

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

         Action

            Run SQL Tuning Advisor on the SELECT statement with SQL_ID

            "d9xccqfmfjvns".

            Related Object

               SQL statement with SQL_ID d9xccqfmfjvns.

               SELECT RCT.CUSTOMER_TRX_ID K_TRX_ID, RCT.TRX_NUMBER, SUM

               (APS.AMOUNT_DUE_REMAINING) CM_AMOUNT_APPLIED,RCT.TRX_DATE FROM

               APPS.RA_CUSTOMER_TRX_ALL RCT, APPS.AR_PAYMENT_SCHEDULES_ALL APS,

               APPS.RA_CUST_TRX_TYPES_ALL TCTTA, APPS.AR_CUSTOMERS AC,

               APPS.HZ_CUSTOMER_PROFILES HCP, APPS.RA_TERMS RT WHERE

               RCT.CUST_TRX_TYPE_ID = TCTTA.CUST_TRX_TYPE_ID AND AC.CUSTOMER_ID =

               RCT.BILL_TO_CUSTOMER_ID AND RCT.CUSTOMER_TRX_ID=APS.CUSTOMER_TRX_ID

               AND AC.CUSTOMER_ID = HCP.CUST_ACCOUNT_ID AND RCT.BILL_TO_SITE_USE_ID

               = HCP.SITE_USE_ID AND RT.TERM_ID = HCP.STANDARD_TERMS AND TCTTA.TYPE

               IN ('INV') AND UPPER(TCTTA.NAME) IN ( SELECT UPPER(MEANING) FROM

               FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = :B3 ) AND RCT.TRX_DATE <= NVL

               (TO_DATE(:B2 , 'RRRR/MM/DD HH24:MI:SS'), RCT.TRX_DATE) AND

               AC.CUSTOMER_ID = :B1 GROUP BY RCT.CUSTOMER_TRX_ID,

               RCT.TRX_NUMBER,RCT.TRX_DATE HAVING SUM (APS.AMOUNT_DUE_REMAINING) > 0

               ORDER BY RCT.TRX_DATE

         Rationale

            The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

            This part of database time may be improved by the SQL Tuning Advisor.

         Rationale

            Database time for this SQL was divided as follows: 100% for SQL

            execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

            execution.

         Rationale

            SQL statement with SQL_ID "d9xccqfmfjvns" was executed 123412 times and

            had an average elapsed time of 0.29 seconds.

         Rationale

            Top level calls to execute the PL/SQL statement with SQL_ID

            "bcxz2s8wj5spc" are responsible for 100% of the database time spent on

            the SELECT statement with SQL_ID "d9xccqfmfjvns".

            Related Object

               SQL statement with SQL_ID bcxz2s8wj5spc.

               BEGIN XXSD_APPLY_CM_APPLY_TO_INV(:errbuf,:rc,:A0,:A1,:A2,:A3); END;

       

         Recommendation 3: SQL Tuning

         Estimated benefit is .3 active sessions, 6.04% of total activity.

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

         Action

            Run SQL Tuning Advisor on the SELECT statement with SQL_ID

            "2ujt2fg615ymk".

            Related Object

               SQL statement with SQL_ID 2ujt2fg615ymk.

               SELECT HP.PARTY_NAME PARTY_NAME, RCT.TRX_NUMBER TRX_NUMBER,

               RCT.TRX_DATE TRX_DATE, APS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL,

               APS.AMOUNT_DUE_REMAINING OUTSTANDING_AMOUNT, RCTT.NAME

               TRANSACTION_TYPE FROM RA_CUSTOMER_TRX_ALL RCT,

               AR_PAYMENT_SCHEDULES_ALL APS, RA_CUST_TRX_TYPES_ALL RCTT,

               HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP, APPS.AR_CUSTOMERS AC,

               APPS.HZ_CUSTOMER_PROFILES HCP, APPS.RA_TERMS RT, HZ_PARTY_SITES HPS,

               HZ_CUST_ACCT_SITES_ALL HCASA, HZ_CUST_SITE_USES_ALL HCSUA WHERE 1 = 1

               AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID AND HCA.PARTY_ID =

               HP.PARTY_ID AND APS.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID AND

               APS.ORG_ID = RCT.ORG_ID AND RCTT.CUST_TRX_TYPE_ID =

               RCT.CUST_TRX_TYPE_ID AND RCTT.ORG_ID = RCT.ORG_ID AND AC.CUSTOMER_ID

               = HCA.CUST_ACCOUNT_ID AND AC.CUSTOMER_ID = RCT.BILL_TO_CUSTOMER_ID

               AND AC.CUSTOMER_ID = HCP.CUST_ACCOUNT_ID AND RCT.BILL_TO_SITE_USE_ID

               = HCP.SITE_USE_ID AND RT.TERM_ID = HCP.STANDARD_TERMS AND

               HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID AND HCASA.CUST_ACCOUNT_ID =

               AC.CUSTOMER_ID AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID

               AND HCSUA.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID AND RCT.TRX_DATE

               BETWEEN NVL (TO_DATE (:B6 , 'RRRR/MM/DD HH24:MI:SS' ), '1-JAN-1900' )

               AND NVL (TO_DATE (:B5 , 'RRRR/MM/DD HH24:MI:SS' ), '31-DEC-2100' )

               AND RCTT.NAME = NVL (:B4 , RCTT.NAME) AND RT.TERM_ID = NVL (:B3 ,

               RT.TERM_ID) AND HP.PARTY_NAME = NVL (:B2 , HP.PARTY_NAME) AND ( (CASE

               WHEN :B1 = 'Open' THEN APS.AMOUNT_DUE_REMAINING END != 0 ) OR ((CASE

               WHEN :B1 = 'Closed' THEN APS.AMOUNT_DUE_REMAINING END = 0 ) ) OR CASE

               WHEN :B1 IS NULL THEN 1 END = 1 )

         Rationale

            The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

            This part of database time may be improved by the SQL Tuning Advisor.

         Rationale

            Database time for this SQL was divided as follows: 100% for SQL

            execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

            execution.

         Rationale

            SQL statement with SQL_ID "2ujt2fg615ymk" was executed 19 times and had

            an average elapsed time of 1368 seconds.

         Rationale

            At least 2 distinct execution plans were utilized for this SQL statement

            during the analysis period.

         Rationale

            Top level calls to execute the PL/SQL statement with SQL_ID

            "g969dx1d0uucf" are responsible for 100% of the database time spent on

            the SELECT statement with SQL_ID "2ujt2fg615ymk".

            Related Object

               SQL statement with SQL_ID g969dx1d0uucf.

               BEGIN XXSD_TRX_DETAIL_REPORT(:errbuf,:rc,:A0,:A1,:A2,:A3,:A4,:A5);

               END;

       

         Recommendation 4: SQL Tuning

         Estimated benefit is .2 active sessions, 4.04% of total activity.

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

         Action

            Run SQL Tuning Advisor on the SELECT statement with SQL_ID

            "gy02p15c6bf0w".

            Related Object

               SQL statement with SQL_ID gy02p15c6bf0w.

               SELECT SUM (RECEIPTS) -  SUM (PAYMENTS)  FROM (SELECT

               AIP.ACCOUNTING_DATE GL_DATE , DECODE(NVL(APC.VENDOR_NAME ,

               APC.REMIT_TO_SUPPLIER_NAME )  , 'FD Investment' , 'FD' , 'Payment' ) 

               TYPE , APC.DOC_SEQUENCE_VALUE VOUCHER_NO , APC.CREATION_DATE

               VOUCHER_DATE , TO_CHAR (APC.CHECK_NUMBER )  CHECK_NUMBER , TO_CHAR

               (NVL(APC.FUTURE_PAY_DUE_DATE , APC.CHECK_DATE )  , 'DD-MON-YYYY' ) 

               CHECK_DATE , NVL(APC.VENDOR_NAME , APC.REMIT_TO_SUPPLIER_NAME ) 

               VENDOR_NAME , API.ACCTS_PAY_CODE_COMBINATION_ID ACCOUNT_CODE ,

               'Supplier Payment' DESCRIPTION , TO_CHAR (DECODE(SIGN(SUM

               (AIP.AMOUNT) )  , -1 , SUM (AIP.AMOUNT *  NVL(AIP.EXCHANGE_RATE , 1 )

               ) *  -1  , SUM (0) )  )  RECEIPTS , TO_CHAR (DECODE(SIGN(SUM

               (AIP.AMOUNT) )  , 1 , SUM (AIP.AMOUNT *  NVL(AIP.EXCHANGE_RATE , 1 ) 

               ) , SUM (0) )  )  PAYMENTS , TO_NUMBER (SUM (AIP.AMOUNT * 

               NVL(AIP.EXCHANGE_RATE , 1 )  ) )  PAYMENTS1 , CBA.BANK_ACCOUNT_NAME

               BANK_ACCOUNT_NAME , CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM ,

               BANK.PARTY_NAME BANK_NAME , BRANCH.PARTY_NAME BRANCH_NAME FROM

               APPS.AP_INVOICE_PAYMENTS_ALL AIP , APPS.AP_INVOICES_ALL API ,

               APPS.AP_CHECKS_ALL APC , APPS.CE_BANK_ACCOUNTS CBA ,

               APPS.CE_BANK_ACCT_USES_ALL CBU , HZ_PARTIES BANK , HZ_PARTIES BRANCH 

               WHERE API.INVOICE_ID =  AIP.INVOICE_ID  AND AIP.CHECK_ID = 

               APC.CHECK_ID  AND APC.CE_BANK_ACCT_USE_ID =  CBU.BANK_ACCT_USE_ID 

               AND CBA.BANK_ACCOUNT_ID =  CBU.BANK_ACCOUNT_ID  AND CBA.BANK_ID = 

               BANK.PARTY_ID  AND CBA.BANK_BRANCH_ID =  BRANCH.PARTY_ID  AND ( (

               APC.STATUS_LOOKUP_CODE IN ( 'NEGOTIABLE' , 'CLEARED' , 'VOIDED' ,

               'RECONCILED UNACCOUNTED' , 'RECONCILED' , 'CLEARED BUT UNACCOUNTED' )

               AND AIP.ACCOUNTING_DATE <  :b1  AND APC.FUTURE_PAY_DUE_DATE IS NULL 

               )  OR ( APC.STATUS_LOOKUP_CODE IN ( 'NEGOTIABLE' )  AND

               APC.FUTURE_PAY_DUE_DATE <  :b1 ) )  AND CBA.BANK_ACCOUNT_ID =  :b2 

               AND AIP.INVOICE_PAYMENT_TYPE IS NULL   AND ( API.ORG_ID IS NULL   OR

               API.ORG_ID =  :b3 )   GROUP BY AIP.ACCOUNTING_DATE ,

               DECODE(NVL(APC.VENDOR_NAME , APC.REMIT_TO_SUPPLIER_NAME )  , 'FD

               Investment' , 'FD' , 'Payment' )  , APC.DOC_SEQUENCE_VALUE ,

               APC.CREATION_DATE , TO_CHAR (APC.CHECK_NUMBER )  , TO_CHAR

               (NVL(APC.FUTURE_PAY_DUE_DATE , APC.CHECK_DATE )  , 'DD-MON-YYYY' )  ,

               NVL(APC.VENDOR_NAME , APC.REMIT_TO_SUPPLIER_NAME )  ,

               API.ACCTS_PAY_CODE_COMBINATION_ID , 'Supplier Payment' ,

               CBA.BANK_ACCOUNT_NAME , CBA.BANK_ACCOUNT_NUM , BANK.PARTY_NAME ,

               BRANCH.PARTY_NAME  UNION ALL SELECT APID.ACCOUNTING_DATE GL_DATE ,

               DECODE(POV.VENDOR_NAME , 'FD Investment' , 'FD' , 'I' )  TYPE ,

               API.DOC_SEQUENCE_VALUE VOUCHER_NO , API.CREATION_DATE VOUCHER_DATE ,

               API.INVOICE_NUM CHECK_NUMBER , TO_CHAR (API.INVOICE_DATE ,

               'DD-MON-YYYY' )  CHECK_DATE , POV.VENDOR_NAME VENDOR_NAME ,

               API.ACCTS_PAY_CODE_COMBINATION_ID ACCOUNT_CODE , NVL(API.DESCRIPTION

               , API.INVOICE_NUM )  DESCRIPTION , TO_CHAR (SUM (API.INVOICE_AMOUNT *

               NVL(API.EXCHANGE_RATE , 1 )  ) )  RECEIPTS , TO_CHAR (SUM (0) ,

               'fm999999999990.00' )  PAYMENTS , TO_NUMBER (SUM (0) )  PAYMENTS1 ,

               CBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME , CBA.BANK_ACCOUNT_NUM

               BANK_ACCOUNT_NUM , BANK.PARTY_NAME BANK_NAME , BRANCH.PARTY_NAME

               BRANCH_NAME FROM APPS.AP_INVOICE_DISTRIBUTIONS_ALL APID ,

               APPS.AP_INVOICE_LINES_ALL APLA , APPS.AP_INVOICES_ALL API ,

               APPS.PO_VENDORS POV , APPS.CE_BANK_ACCOUNTS CBA , HZ_PARTIES BANK ,

               HZ_PARTIES BRANCH   WHERE API.INVOICE_ID =  APID.INVOICE_ID  AND

               APLA.INVOICE_ID =  APID.INVOICE_ID  AND APLA.LINE_NUMBER = 

               APID.INVOICE_LINE_NUMBER  AND API.VENDOR_ID =  POV.VENDOR_ID  AND

               CBA.BANK_ACCOUNT_ID =  :b2  AND CBA.ASSET_CODE_COMBINATION_ID = 

               APID.DIST_CODE_COMBINATION_ID  AND CBA.BANK_ID =  BANK.PARTY_ID  AND

               CBA.BANK_BRANCH_ID =  BRANCH.PARTY_ID  AND APID.MATCH_STATUS_FLAG = 

               'A'  AND NVL(API.PAYMENT_STATUS_FLAG , 'N' )  !=  'N'  AND

               APID.ACCOUNTING_DATE <  :b1  AND ( API.ORG_ID IS NULL   OR API.ORG_ID

               =  :b3 )   GROUP BY APID.ACCOUNTING_DATE , DECODE(POV.VENDOR_NAME ,

               'FD Investment' , 'FD' , 'I' )  , API.DOC_SEQUENCE_VALUE ,

               API.CREATION_DATE , API.INVOICE_NUM , TO_CHAR (API.INVOICE_DATE ,

               'DD-MON-YYYY' )  , POV.VENDOR_NAME ,

               API.ACCTS_PAY_CODE_COMBINATION_ID , NVL(API.DESCRIPTION ,

               API.INVOICE_NUM )  , CBA.BANK_ACCOUNT_NAME , CBA.BANK_ACCOUNT_NUM ,

               BANK.PARTY_NAME , BRANCH.PARTY_NAME  UNION ALL SELECT ACRH.GL_DATE

               GL_DATE , DECODE(ACRH.STATUS , 'REVERSED' , 'REV' ,

               DECODE(ACRH.CREATED_FROM , 'RATE ADJUSTMENT TRIGGER' , 'ERF' ,

               'Receipt' )  )  TYPE , ACR.DOC_SEQUENCE_VALUE VOUCHER_NO ,

               ACR.CREATION_DATE VOUCHER_DATE , ACR.RECEIPT_NUMBER CHECK_NUMBER ,

               TO_CHAR (ACR.RECEIPT_DATE , 'DD-MON-YYYY' )  CHECK_DATE ,

               NVL(SUBSTR(HP.PARTY_NAME , 1 , 50 )  , 'Miscellaneous Activity' ) 

               VENDOR_NAME , ACRH.ACCOUNT_CODE_COMBINATION_ID ACCOUNT_CODE ,

               'Customer Receipt' DESCRIPTION , TO_CHAR (SUM (DECODE(ACRH.STATUS ,

               'REVERSED' , NVL(ADA.ACCTD_AMOUNT_DR , 0 )  , 'CLEARED' ,

               NVL(ADA.ACCTD_AMOUNT_DR , 0 )  , 'REMITTED' , NVL(ADA.ACCTD_AMOUNT_DR

               , 0 )  , 'CONFIRMED' , NVL(ADA.ACCTD_AMOUNT_DR , 0 )  ) ) ,

               'fm999999999990.00' )  RECEIPTS , TO_CHAR (SUM (DECODE(ACRH.STATUS ,

               'REVERSED' , NVL(ADA.ACCTD_AMOUNT_CR , 0 )  , 'CLEARED' ,

               NVL(ADA.ACCTD_AMOUNT_CR , 0 )  , 'REMITTED' , NVL(ADA.ACCTD_AMOUNT_CR

               , 0 )  , 'CONFIRMED' , NVL(ADA.ACCTD_AMOUNT_CR , 0 )  ) ) ,

               'fm999999999990.00' )  PAYMENTS , TO_NUMBER (TO_CHAR (SUM (0) )  ) 

               PAYMENTS1 , CBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME ,

               CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM , BANK.PARTY_NAME BANK_NAME ,

               BRANCH.PARTY_NAME BRANCH_NAME FROM APPS.AR_CASH_RECEIPT_HISTORY_ALL

               ACRH , APPS.AR_CASH_RECEIPTS_ALL ACR , APPS.HZ_PARTIES HP ,

               APPS.HZ_CUST_ACCOUNTS HCA , APPS.CE_BANK_ACCOUNTS CBA ,

               APPS.CE_BANK_ACCT_USES_ALL CBAU , APPS.AR_DISTRIBUTIONS_ALL ADA ,

               HZ_PARTIES BANK , HZ_PARTIES BRANCH   WHERE ACRH.CASH_RECEIPT_ID = 

               ACR.CASH_RECEIPT_ID  AND CBAU.BANK_ACCT_USE_ID = 

               ACR.REMIT_BANK_ACCT_USE_ID  AND CBAU.BANK_ACCOUNT_ID = 

               CBA.BANK_ACCOUNT_ID  AND HCA.PARTY_ID =  HP.PARTY_ID     AND

               ACR.PAY_FROM_CUSTOMER =  HCA.CUST_ACCOUNT_ID     AND CBA.BANK_ID =

               BANK.PARTY_ID  AND CBA.BANK_BRANCH_ID =  BRANCH.PARTY_ID  AND

               ACRH.STATUS IN ( 'CLEARED' , 'REMITTED' , 'CONFIRMED' , 'REVERSED' ) 

               AND ACRH.CASH_RECEIPT_HISTORY_ID =  ADA.SOURCE_ID  AND

               ADA.SOURCE_TABLE =  'CRH'  AND ADA.SOURCE_TYPE !=  'BANK_CHARGES' 

               AND CBA.BANK_ACCOUNT_ID =  :b2  AND ACRH.GL_DATE <  :b1  AND (

               ACR.ORG_ID IS NULL   OR ACR.ORG_ID =  :b3 )  AND ( (

               ACR.REVERSAL_DATE IS NOT NULL   AND ACRH.STATUS =  'REVERSED' )  OR (

               ACRH.CASH_RECEIPT_HISTORY_ID IN (SELECT INCRH.CASH_RECEIPT_HISTORY_ID

               FROM APPS.AR_CASH_RECEIPT_HISTORY_ALL INCRH   WHERE

               INCRH.CASH_RECEIPT_ID =  ACR.CASH_RECEIPT_ID  AND INCRH.STATUS != 

               'REVERSED'   )) )   GROUP BY ACRH.GL_DATE , DECODE(ACRH.STATUS ,

               'REVERSED' , 'REV' , DECODE(ACRH.CREATED_FROM , 'RATE ADJUSTMENT

               TRIGGER' , 'ERF' , 'Receipt' )  )  , ACR.DOC_SEQUENCE_VALUE ,

               ACR.CREATION_DATE , ACR.RECEIPT_NUMBER , TO_CHAR (ACR.RECEIPT_DATE ,

               'DD-MON-YYYY' )  , NVL(SUBSTR(HP.PARTY_NAME , 1 , 50 )  ,

               'Miscellaneous Activity' )  , ACRH.ACCOUNT_CODE_COMBINATION_ID ,

               'Customer Receipt' , CBA.BANK_ACCOUNT_NAME , CBA.BANK_ACCOUNT_NUM ,

               BANK.PARTY_NAME , BRANCH.PARTY_NAME  UNION ALL SELECT ACRH.GL_DATE

               GL_DATE , DECODE(ADJ.REASON_CODE ,  NULL  , 'ADJ' , ADJ.REASON_CODE

               ||  ' - ADJ'  )  TYPE , ACR.DOC_SEQUENCE_VALUE VOUCHER_NO ,

               ACR.CREATION_DATE VOUCHER_DATE ,  NULL  CHECK_NUMBER ,  NULL 

               CHECK_DATE , NVL(SUBSTR(HP.PARTY_NAME , 1 , 50 )  , 'Miscellaneous

               Activity' )  VENDOR_NAME , ACRH.ACCOUNT_CODE_COMBINATION_ID

               ACCOUNT_CODE , NVL(ACR.COMMENTS , ACR.RECEIPT_NUMBER )  DESCRIPTION ,

               TO_CHAR (SUM (-1 *  ADJ.AMOUNT ) , 'fm999999999990.00' )  RECEIPTS , 

               NULL  PAYMENTS , TO_NUMBER (TO_CHAR (SUM (0) )  )  PAYMENTS1 ,

               CBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME , CBA.BANK_ACCOUNT_NUM

               BANK_ACCOUNT_NUM , BANK.PARTY_NAME BANK_NAME , BRANCH.PARTY_NAME

               BRANCH_NAME FROM APPS.AR_CASH_RECEIPT_HISTORY_ALL ACRH ,

               APPS.AR_CASH_RECEIPTS_ALL ACR , APPS.HZ_PARTIES HP ,

               APPS.HZ_CUST_ACCOUNTS HCA , APPS.CE_BANK_ACCOUNTS CBA ,

               APPS.CE_BANK_ACCT_USES_ALL CBAU , APPS.AR_RECEIVABLE_APPLICATIONS_ALL

               APP , APPS.AR_ADJUSTMENTS_ALL ADJ , HZ_PARTIES BANK , HZ_PARTIES

               BRANCH   WHERE ACRH.CASH_RECEIPT_ID =  ACR.CASH_RECEIPT_ID  AND

               APP.CASH_RECEIPT_ID =  ACRH.CASH_RECEIPT_ID  AND APP.DISPLAY =  'Y' 

               AND APP.STATUS =  'APP'  AND APP.APPLICATION_TYPE =  'CASH'  AND

               ADJ.CUSTOMER_TRX_ID =  APP.APPLIED_CUSTOMER_TRX_ID  AND

               ADJ.CHARGEBACK_CUSTOMER_TRX_ID IS NULL   AND ADJ.TYPE =  'CHARGES' 

               AND CBAU.BANK_ACCT_USE_ID =  ACR.REMIT_BANK_ACCT_USE_ID  AND

               CBAU.BANK_ACCOUNT_ID =  CBA.BANK_ACCOUNT_ID  AND ACRH.CASH_RECEIPT_ID

               =  ACR.CASH_RECEIPT_ID  AND HCA.PARTY_ID =  HP.PARTY_ID     AND

               ACR.PAY_FROM_CUSTOMER =  HCA.CUST_ACCOUNT_ID     AND CBA.BANK_ID =

               BANK.PARTY_ID  AND CBA.BANK_BRANCH_ID =  BRANCH.PARTY_ID  AND

               ACRH.STATUS IN ( 'CLEARED' , 'REMITTED' , 'CONFIRMED' , 'REVERSED' ) 

               AND CBA.BANK_ACCOUNT_ID =  :b2  AND ACRH.GL_DATE <  :b1  AND (

               ACR.ORG_ID IS NULL   OR ACR.ORG_ID =  :b3 )  AND ( (

               ACR.REVERSAL_DATE IS NOT NULL   AND ACRH.STATUS =  'REVERSED' )  OR (

               ACRH.CASH_RECEIPT_HISTORY_ID IN (SELECT MIN

               (INCRH.CASH_RECEIPT_HISTORY_ID) FROM APPS.AR_CASH_RECEIPT_HISTORY_ALL

               INCRH   WHERE INCRH.CASH_RECEIPT_ID =  ACR.CASH_RECEIPT_ID  AND

               INCRH.STATUS !=  'REVERSED'   )) )   GROUP BY ACRH.GL_DATE ,

               DECODE(ADJ.REASON_CODE ,  NULL  , 'ADJ' , ADJ.REASON_CODE ||  ' -

               ADJ'  )  , ACR.DOC_SEQUENCE_VALUE , ACR.CREATION_DATE ,  NULL  , 

               NULL  , NVL(SUBSTR(HP.PARTY_NAME , 1 , 50 )  , 'Miscellaneous

               Activity' )  , ACRH.ACCOUNT_CODE_COMBINATION_ID , NVL(ACR.COMMENTS ,

               ACR.RECEIPT_NUMBER )  ,  NULL  , CBA.BANK_ACCOUNT_NAME ,

               CBA.BANK_ACCOUNT_NUM , BANK.PARTY_NAME , BRANCH.PARTY_NAME  UNION ALL

               SELECT PT.TRANSACTION_DATE GL_DATE , DECODE(PT.CREATED_FROM_DIR ,

               'PAYMENT' , 'Payment' )  TYPE , PT.TRXN_REFERENCE_NUMBER VOUCHER_NO ,

               TRUNC (PT.CREATION_DATE )  VOUCHER_DATE , PT.BANK_TRXN_NUMBER

               CHECK_NUMBER , TO_CHAR (PT.TRANSACTION_DATE , 'DD-MON-YYYY' ) 

               CHECK_DATE , DESTBA.BANK_ACCOUNT_NAME VENDOR_NAME ,

               GCC.CODE_COMBINATION_ID ACCOUNT_CODE , 'Inter Bank Transfer' ACC_DESC

               , '0' RECEIPTS , TO_CHAR (SUM (PT.PAYMENT_AMOUNT) )  PAYMENTS ,

               TO_NUMBER (SUM (PT.PAYMENT_AMOUNT) )  PAYMENTS1 ,

               SRCBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME , SRCBA.BANK_ACCOUNT_NUM

               BANK_ACCOUNT_NUM , BANK.PARTY_NAME BANK_NAME , BRANCH.PARTY_NAME

               BRANCH_NAME FROM APPS.HZ_ORGANIZATION_PROFILES SBANKORGPROFILE ,

               APPS.HZ_CODE_ASSIGNMENTS SBANKCA , APPS.HZ_PARTIES SBRANCHPARTY ,

               APPS.HZ_ORGANIZATION_PROFILES SBRANCHORGPROFILE ,

               APPS.HZ_CODE_ASSIGNMENTS SBRANCHCA , APPS.HZ_RELATIONSHIPS SBRREL ,

               APPS.HZ_CODE_ASSIGNMENTS SBRANCHTYPECA , APPS.HZ_CONTACT_POINTS

               SBRANCHCP , APPS.HZ_CONTACT_POINTS SEDICP ,

               APPS.HZ_ORGANIZATION_PROFILES DBANKORGPROFILE ,

               APPS.HZ_CODE_ASSIGNMENTS DBANKCA , APPS.HZ_PARTIES DBRANCHPARTY ,

               APPS.HZ_ORGANIZATION_PROFILES DBRANCHORGPROFILE ,

               APPS.HZ_CODE_ASSIGNMENTS DBRANCHCA , APPS.HZ_RELATIONSHIPS DBRREL ,

               APPS.HZ_CODE_ASSIGNMENTS DBRANCHTYPECA , APPS.HZ_CONTACT_POINTS

               DBRANCHCP , APPS.HZ_CONTACT_POINTS DEDICP ,

               APPS.CE_PAYMENT_TRANSACTIONS PT , APPS.CE_BANK_ACCOUNTS SRCBA ,

               APPS.CE_BANK_ACCOUNTS DESTBA , APPS.XLE_ENTITY_PROFILES SRCLE ,

               APPS.XLE_ENTITY_PROFILES DESTLE , APPS.GL_CODE_COMBINATIONS GCC ,

               APPS.HZ_PARTIES HZP , HZ_PARTIES BANK , HZ_PARTIES BRANCH   WHERE

               SYSDATE  BETWEEN TRUNC (SBANKORGPROFILE.EFFECTIVE_START_DATE )  AND

               NVL(TRUNC (SBANKORGPROFILE.EFFECTIVE_END_DATE )  , SYSDATE  +  1  )  

               AND SBANKCA.CLASS_CATEGORY =  'BANK_INSTITUTION_TYPE'  AND

               SBANKCA.CLASS_CODE IN ( 'BANK' , 'CLEARINGHOUSE' )  AND

               SBANKCA.OWNER_TABLE_NAME =  'HZ_PARTIES'  AND ( SBANKCA.STATUS =  'A'

               OR SBANKCA.STATUS IS NULL  )  AND SBANKCA.OWNER_TABLE_ID = 

               SBANKORGPROFILE.PARTY_ID  AND SBRANCHPARTY.PARTY_TYPE = 

               'ORGANIZATION'  AND SBRANCHPARTY.STATUS =  'A'  AND

               SBRANCHORGPROFILE.PARTY_ID =  SBRANCHPARTY.PARTY_ID  AND SYSDATE 

               BETWEEN TRUNC (SBRANCHORGPROFILE.EFFECTIVE_START_DATE )  AND

               NVL(TRUNC (SBRANCHORGPROFILE.EFFECTIVE_END_DATE )  , SYSDATE  +  1  )

               AND SBRANCHCA.CLASS_CATEGORY =  'BANK_INSTITUTION_TYPE'  AND

               SBRANCHCA.CLASS_CODE IN ( 'BANK_BRANCH' , 'CLEARINGHOUSE_BRANCH' ) 

               AND SBRANCHCA.OWNER_TABLE_NAME =  'HZ_PARTIES'  AND (

               SBRANCHCA.STATUS =  'A'  OR SBRANCHCA.STATUS IS NULL  )  AND

               SBRANCHCA.OWNER_TABLE_ID =  SBRANCHPARTY.PARTY_ID  AND

               SBANKORGPROFILE.PARTY_ID =  SBRREL.OBJECT_ID  AND

               SBRREL.RELATIONSHIP_TYPE =  'BANK_AND_BRANCH'  AND

               SBRREL.RELATIONSHIP_CODE =  'BRANCH_OF'  AND SBRREL.STATUS =  'A' 

               AND SBRREL.SUBJECT_TABLE_NAME =  'HZ_PARTIES'  AND

               SBRREL.SUBJECT_TYPE =  'ORGANIZATION'  AND SBRREL.OBJECT_TABLE_NAME =

               'HZ_PARTIES'  AND SBRREL.OBJECT_TYPE =  'ORGANIZATION'  AND

               SBRREL.SUBJECT_ID =  SBRANCHPARTY.PARTY_ID  AND

               SBRANCHTYPECA.CLASS_CATEGORY    =  'BANK_BRANCH_TYPE'  AND

               SBRANCHTYPECA.PRIMARY_FLAG    =  'Y'  AND

               SBRANCHTYPECA.OWNER_TABLE_NAME    =  'HZ_PARTIES'  AND

               SBRANCHTYPECA.OWNER_TABLE_ID    =  SBRANCHPARTY.PARTY_ID  AND

               SBRANCHTYPECA.STATUS    =  'A'  AND SBRANCHCP.OWNER_TABLE_NAME

               =  'HZ_PARTIES'  AND SBRANCHCP.OWNER_TABLE_ID    = 

               SBRANCHPARTY.PARTY_ID  AND SBRANCHCP.CONTACT_POINT_TYPE    = 

               'EFT'  AND SBRANCHCP.STATUS    =  'A'  AND SEDICP.OWNER_TABLE_NAME

                  =  'HZ_PARTIES'  AND SEDICP.OWNER_TABLE_ID    = 

               SBRANCHPARTY.PARTY_ID  AND SEDICP.CONTACT_POINT_TYPE    =  'EDI' 

               AND SEDICP.STATUS    =  'A'  AND SYSDATE  BETWEEN TRUNC

               (DBANKORGPROFILE.EFFECTIVE_START_DATE )  AND NVL(TRUNC

               (DBANKORGPROFILE.EFFECTIVE_END_DATE )  , SYSDATE  +  1  )   AND

               DBANKCA.CLASS_CATEGORY =  'BANK_INSTITUTION_TYPE'  AND

               DBANKCA.CLASS_CODE IN ( 'BANK' , 'CLEARINGHOUSE' )  AND

               DBANKCA.OWNER_TABLE_NAME =  'HZ_PARTIES'  AND ( DBANKCA.STATUS =  'A'

               OR DBANKCA.STATUS IS NULL  )  AND DBANKCA.OWNER_TABLE_ID = 

               DBANKORGPROFILE.PARTY_ID  AND DBRANCHPARTY.PARTY_TYPE = 

               'ORGANIZATION'  AND DBRANCHPARTY.STATUS =  'A'  AND

               DBRANCHORGPROFILE.PARTY_ID =  DBRANCHPARTY.PARTY_ID  AND SYSDATE 

               BETWEEN TRUNC (DBRANCHORGPROFILE.EFFECTIVE_START_DATE )  AND

               NVL(TRUNC (DBRANCHORGPROFILE.EFFECTIVE_END_DATE )  , SYSDATE  +  1  )

               AND DBRANCHCA.CLASS_CATEGORY =  'BANK_INSTITUTION_TYPE'  AND

               DBRANCHCA.CLASS_CODE IN ( 'BANK_BRANCH' , 'CLEARINGHOUSE_BRANCH' ) 

               AND DBRANCHCA.OWNER_TABLE_NAME =  'HZ_PARTIES'  AND (

               DBRANCHCA.STATUS =  'A'  OR DBRANCHCA.STATUS IS NULL  )  AND

               DBRANCHCA.OWNER_TABLE_ID =  DBRANCHPARTY.PARTY_ID  AND

               DBANKORGPROFILE.PARTY_ID =  DBRREL.OBJECT_ID  AND

               DBRREL.RELATIONSHIP_TYPE =  'BANK_AND_BRANCH'  AND

               DBRREL.RELATIONSHIP_CODE =  'BRANCH_OF'  AND DBRREL.STATUS =  'A' 

               AND DBRREL.SUBJECT_TABLE_NAME =  'HZ_PARTIES'  AND

               DBRREL.SUBJECT_TYPE =  'ORGANIZATION'  AND DBRREL.OBJECT_TABLE_NAME =

               'HZ_PARTIES'  AND DBRREL.OBJECT_TYPE =  'ORGANIZATION'  AND

               DBRREL.SUBJECT_ID =  DBRANCHPARTY.PARTY_ID  AND

               DBRANCHTYPECA.CLASS_CATEGORY    =  'BANK_BRANCH_TYPE'  AND

               DBRANCHTYPECA.PRIMARY_FLAG    =  'Y'  AND

               DBRANCHTYPECA.OWNER_TABLE_NAME    =  'HZ_PARTIES'  AND

               DBRANCHTYPECA.OWNER_TABLE_ID    =  DBRANCHPARTY.PARTY_ID  AND

               DBRANCHTYPECA.STATUS    =  'A'  AND DBRANCHCP.OWNER_TABLE_NAME

               =  'HZ_PARTIES'  AND DBRANCHCP.OWNER_TABLE_ID    = 

               DBRANCHPARTY.PARTY_ID  AND DBRANCHCP.CONTACT_POINT_TYPE    = 

               'EFT'  AND DBRANCHCP.STATUS    =  'A'  AND DEDICP.OWNER_TABLE_NAME

                  =  'HZ_PARTIES'  AND DEDICP.OWNER_TABLE_ID    = 

               DBRANCHPARTY.PARTY_ID  AND DEDICP.CONTACT_POINT_TYPE    =  'EDI' 

               AND DEDICP.STATUS    =  'A'  AND PT.SOURCE_BANK_ACCOUNT_ID = 

               SRCBA.BANK_ACCOUNT_ID  AND SRCBA.BANK_ID =  BANK.PARTY_ID  AND

               SRCBA.BANK_BRANCH_ID =  BRANCH.PARTY_ID  AND

               PT.SOURCE_BANK_ACCOUNT_ID =  :b2  AND SRCBA.BANK_BRANCH_ID = 

               SBRANCHPARTY.PARTY_ID  AND SRCLE.LEGAL_ENTITY_ID = 

               PT.SOURCE_LEGAL_ENTITY_ID  AND PT.DESTINATION_BANK_ACCOUNT_ID = 

               DESTBA.BANK_ACCOUNT_ID  AND DESTBA.BANK_BRANCH_ID = 

               DBRANCHPARTY.PARTY_ID  AND DESTLE.LEGAL_ENTITY_ID = 

               PT.DESTINATION_LEGAL_ENTITY_ID  AND PT.TRANSACTION_DATE <  :b1  AND

               PT.TRXN_STATUS_CODE IN ( 'SETTLED' , 'VALIDATED' )  AND

               PT.CREATED_FROM_DIR =  'PAYMENT'  AND GCC.CODE_COMBINATION_ID = 

               SRCBA.ASSET_CODE_COMBINATION_ID  AND SRCLE.PARTY_ID =  HZP.PARTY_ID  

               GROUP BY PT.TRANSACTION_DATE , PT.CREATED_FROM_DIR , PT.CREATION_DATE

               , PT.SETTLE_BY_SYSTEM_FLAG , PT.BANK_TRXN_NUMBER ,

               PT.PAYMENT_REQUEST_NUMBER , DESTBA.BANK_ACCOUNT_NAME ,

               PT.TRXN_REFERENCE_NUMBER , GCC.CODE_COMBINATION_ID , GCC.SEGMENT6 ,

               SRCBA.BANK_ACCOUNT_NAME , SRCBA.BANK_ACCOUNT_NUM , BANK.PARTY_NAME ,

               BRANCH.PARTY_NAME  UNION ALL SELECT PT.TRANSACTION_DATE GL_DATE ,

               'Receipt' TYPE , PT.TRXN_REFERENCE_NUMBER VOUCHER_NO , TRUNC

               (PT.CREATION_DATE )  VOUCHER_DATE , PT.BANK_TRXN_NUMBER CHECK_NUMBER

               , TO_CHAR (PT.TRANSACTION_DATE , 'DD-MON-YYYY' )  CHECK_DATE ,

               DESTBA.BANK_ACCOUNT_NAME VENDOR_NAME , GCC.CODE_COMBINATION_ID

               ACCOUNT_CODE , 'Inter Bank Transfer' ACC_DESC , TO_CHAR (SUM

               (PT.PAYMENT_AMOUNT) )  RECEIPTS , '0' PAYMENTS , TO_NUMBER (SUM

               (PT.PAYMENT_AMOUNT) *  -1  )  PAYMENTS1 , SRCBA.BANK_ACCOUNT_NAME

               BANK_ACCOUNT_NAME , SRCBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM ,

               BANK.PARTY_NAME BANK_NAME , BRANCH.PARTY_NAME BRANCH_NAME FROM

               APPS.HZ_ORGANIZATION_PROFILES SBANKORGPROFILE ,

               APPS.HZ_CODE_ASSIGNMENTS SBANKCA , APPS.HZ_PARTIES SBRANCHPARTY ,

               APPS.HZ_ORGANIZATION_PROFILES SBRANCHORGPROFILE ,

               APPS.HZ_CODE_ASSIGNMENTS SBRANCHCA , APPS.HZ_RELATIONSHIPS SBRREL ,

               APPS.HZ_CODE_ASSIGNMENTS SBRANCHTYPECA , APPS.HZ_CONTACT_POINTS

               SBRANCHCP , APPS.HZ_CONTACT_POINTS SEDICP ,

               APPS.HZ_ORGANIZATION_PROFILES DBANKORGPROFILE ,

               APPS.HZ_CODE_ASSIGNMENTS DBANKCA , APPS.HZ_PARTIES DBRANCHPARTY ,

               APPS.HZ_ORGANIZATION_PROFILES DBRANCHORGPROFILE ,

               APPS.HZ_CODE_ASSIGNMENTS DBRANCHCA , APPS.HZ_RELATIONSHIPS DBRREL ,

               APPS.HZ_CODE_ASSIGNMENTS DBRANCHTYPECA , APPS.HZ_CONTACT_POINTS

               DBRANCHCP , APPS.HZ_CONTACT_POINTS DEDICP ,

               APPS.CE_PAYMENT_TRANSACTIONS PT , APPS.CE_BANK_ACCOUNTS SRCBA ,

               APPS.CE_BANK_ACCOUNTS DESTBA , APPS.XLE_ENTITY_PROFILES SRCLE ,

               APPS.XLE_ENTITY_PROFILES DESTLE , APPS.GL_CODE_COMBINATIONS GCC ,

               APPS.HZ_PARTIES HZP , HZ_PARTIES BANK , HZ_PARTIES BRANCH   WHERE

               SYSDATE  BETWEEN TRUNC (SBANKORGPROFILE.EFFECTIVE_START_DATE )  AND

               NVL(TRUNC (SBANKORGPROFILE.EFFECTIVE_END_DATE )  , SYSDATE  +  1  )  

               AND SBANKCA.CLASS_CATEGORY =  'BANK_INSTITUTION_TYPE'  AND

               SBANKCA.CLASS_CODE IN ( 'BANK' , 'CLEARINGHOUSE' )  AND

               SBANKCA.OWNER_TABLE_NAME =  'HZ_PARTIES'  AND ( SBANKCA.STATUS =  'A'

               OR SBANKCA.STATUS IS NULL  )  AND SBANKCA.OWNER_TABLE_ID = 

               SBANKORGPROFILE.PARTY_ID  AND SBRANCHPARTY.PARTY_TYPE = 

               'ORGANIZATION'  AND SBRANCHPARTY.STATUS =  'A'  AND

               SBRANCHORGPROFILE.PARTY_ID =  SBRANCHPARTY.PARTY_ID  AND SYSDATE 

               BETWEEN TRUNC (SBRANCHORGPROFILE.EFFECTIVE_START_DATE )  AND

               NVL(TRUNC (SBRANCHORGPROFILE.EFFECTIVE_END_DATE )  , SYSDATE  +  1  )

               AND SBRANCHCA.CLASS_CATEGORY =  'BANK_INSTITUTION_TYPE'  AND

               SBRANCHCA.CLASS_CODE IN ( 'BANK_BRANCH' , 'CLEARINGHOUSE_BRANCH' ) 

               AND SBRANCHCA.OWNER_TABLE_NAME =  'HZ_PARTIES'  AND (

               SBRANCHCA.STATUS =  'A'  OR SBRANCHCA.STATUS IS NULL  )  AND

               SBRANCHCA.OWNER_TABLE_ID =  SBRANCHPARTY.PARTY_ID  AND

               SBANKORGPROFILE.PARTY_ID =  SBRREL.OBJECT_ID  AND

               SBRREL.RELATIONSHIP_TYPE =  'BANK_AND_BRANCH'  AND

               SBRREL.RELATIONSHIP_CODE =  'BRANCH_OF'  AND SBRREL.STATUS =  'A' 

               AND SBRREL.SUBJECT_TABLE_NAME =  'HZ_PARTIES'  AND

               SBRREL.SUBJECT_TYPE =  'ORGANIZATION'  AND SBRREL.OBJECT_TABLE_NAME =

               'HZ_PARTIES'  AND SBRREL.OBJECT_TYPE =  'ORGANIZATION'  AND

               SBRREL.SUBJECT_ID =  SBRANCHPARTY.PARTY_ID  AND

               SBRANCHTYPECA.CLASS_CATEGORY    =  'BANK_BRANCH_TYPE'  AND

               SBRANCHTYPECA.PRIMARY_FLAG    =  'Y'  AND

               SBRANCHTYPECA.OWNER_TABLE_NAME    =  'HZ_PARTIES'  AND

               SBRANCHTYPECA.OWNER_TABLE_ID    =  SBRANCHPARTY.PARTY_ID  AND

               SBRANCHTYPECA.STATUS    =  'A'  AND SBRANCHCP.OWNER_TABLE_NAME

               =  'HZ_PARTIES'  AND SBRANCHCP.OWNER_TABLE_ID    = 

               SBRANCHPARTY.PARTY_ID  AND SBRANCHCP.CONTACT_POINT_TYPE    = 

               'EFT'  AND SBRANCHCP.STATUS    =  'A'  AND SEDICP.OWNER_TABLE_NAME

                  =  'HZ_PARTIES'  AND SEDICP.OWNER_TABLE_ID    = 

               SBRANCHPARTY.PARTY_ID  AND SEDICP.CONTACT_POINT_TYPE    =  'EDI' 

               AND SEDICP.STATUS    =  'A'  AND SYSDATE  BETWEEN TRUNC

               (DBANKORGPROFILE.EFFECTIVE_START_DATE )  AND NVL(TRUNC

               (DBANKORGPROFILE.EFFECTIVE_END_DATE )  , SYSDATE  +  1  )   AND

               DBANKCA.CLASS_CATEGORY =  'BANK_INSTITUTION_TYPE'  AND

               DBANKCA.CLASS_CODE IN ( 'BANK' , 'CLEARINGHOUSE' )  AND

               DBANKCA.OWNER_TABLE_NAME =  'HZ_PARTIES'  AND ( DBANKCA.STATUS =  'A'

               OR DBANKCA.STATUS IS NULL  )  AND DBANKCA.OWNER_TABLE_ID = 

               DBANKORGPROFILE.PARTY_ID  AND DBRANCHPARTY.PARTY_TYPE = 

               'ORGANIZATION'  AND DBRANCHPARTY.STATUS =  'A'  AND

               DBRANCHORGPROFILE.PARTY_ID =  DBRANCHPARTY.PARTY_ID  AND SYSDATE 

               BETWEEN TRUNC (DBRANCHORGPROFILE.EFFECTIVE_START_DATE )  AND

               NVL(TRUNC (DBRANCHORGPROFILE.EFFECTIVE_END_DATE )  , SYSDATE  +  1  )

               AND DBRANCHCA.CLASS_CATEGORY =  'BANK_INSTITUTION_TYPE'  AND

               DBRANCHCA.CLASS_CODE IN ( 'BANK_BRANCH' , 'CLEARINGHOUSE_BRANCH' ) 

               AND DBRANCHCA.OWNER_TABLE_NAME =  'HZ_PARTIES'  AND (

               DBRANCHCA.STATUS =  'A'  OR DBRANCHCA.STATUS IS NULL  )  AND

               DBRANCHCA.OWNER_TABLE_ID =  DBRANCHPARTY.PARTY_ID  AND

               DBANKORGPROFILE.PARTY_ID =  DBRREL.OBJECT_ID  AND

               DBRREL.RELATIONSHIP_TYPE =  'BANK_AND_BRANCH'  AND

               DBRREL.RELATIONSHIP_CODE =  'BRANCH_OF'  AND DBRREL.STATUS =  'A' 

               AND DBRREL.SUBJECT_TABLE_NAME =  'HZ_PARTIES'  AND

               DBRREL.SUBJECT_TYPE =  'ORGANIZATION'  AND DBRREL.OBJECT_TABLE_NAME =

               'HZ_PARTIES'  AND DBRREL.OBJECT_TYPE =  'ORGANIZATION'  AND

               DBRREL.SUBJECT_ID =  DBRANCHPARTY.PARTY_ID  AND

               DBRANCHTYPECA.CLASS_CATEGORY    =  'BANK_BRANCH_TYPE'  AND

               DBRANCHTYPECA.PRIMARY_FLAG    =  'Y'  AND

               DBRANCHTYPECA.OWNER_TABLE_NAME    =  'HZ_PARTIES'  AND

               DBRANCHTYPECA.OWNER_TABLE_ID    =  DBRANCHPARTY.PARTY_ID  AND

               DBRANCHTYPECA.STATUS    =  'A'  AND DBRANCHCP.OWNER_TABLE_NAME

               =  'HZ_PARTIES'  AND DBRANCHCP.OWNER_TABLE_ID    = 

               DBRANCHPARTY.PARTY_ID  AND DBRANCHCP.CONTACT_POINT_TYPE    = 

               'EFT'  AND DBRANCHCP.STATUS    =  'A'  AND DEDICP.OWNER_TABLE_NAME

                  =  'HZ_PARTIES'  AND DEDICP.OWNER_TABLE_ID    = 

               DBRANCHPARTY.PARTY_ID  AND DEDICP.CONTACT_POINT_TYPE    =  'EDI' 

               AND DEDICP.STATUS    =  'A'  AND PT.DESTINATION_BANK_ACCOUNT_ID = 

               SRCBA.BANK_ACCOUNT_ID  AND SRCBA.BANK_ID =  BANK.PARTY_ID  AND

               SRCBA.BANK_BRANCH_ID =  BRANCH.PARTY_ID  AND

               PT.DESTINATION_BANK_ACCOUNT_ID =  :b2  AND SRCBA.BANK_BRANCH_ID = 

               SBRANCHPARTY.PARTY_ID  AND SRCLE.LEGAL_ENTITY_ID = 

               PT.SOURCE_LEGAL_ENTITY_ID  AND PT.SOURCE_BANK_ACCOUNT_ID = 

               DESTBA.BANK_ACCOUNT_ID  AND DESTBA.BANK_BRANCH_ID = 

               DBRANCHPARTY.PARTY_ID  AND DESTLE.LEGAL_ENTITY_ID = 

               PT.DESTINATION_LEGAL_ENTITY_ID  AND PT.TRANSACTION_DATE <  :b1  AND

               PT.TRXN_STATUS_CODE IN ( 'SETTLED' , 'VALIDATED' )  AND

               PT.CREATED_FROM_DIR =  'PAYMENT'  AND GCC.CODE_COMBINATION_ID = 

               SRCBA.ASSET_CODE_COMBINATION_ID  AND SRCLE.PARTY_ID =  HZP.PARTY_ID  

               GROUP BY PT.TRANSACTION_DATE , PT.CREATED_FROM_DIR , PT.CREATION_DATE

               , PT.BANK_TRXN_NUMBER , DESTBA.BANK_ACCOUNT_NAME ,

               PT.TRXN_REFERENCE_NUMBER , GCC.CODE_COMBINATION_ID , GCC.SEGMENT6 ,

               SRCBA.BANK_ACCOUNT_NAME , SRCBA.BANK_ACCOUNT_NUM , BANK.PARTY_NAME ,

               BRANCH.PARTY_NAME  UNION ALL SELECT GLH.DEFAULT_EFFECTIVE_DATE

               GL_DATE , 'Journal' TYPE , GLH.DOC_SEQUENCE_VALUE VOUCHER_NO ,

               GLH.DEFAULT_EFFECTIVE_DATE VOUCHER_DATE ,  NULL  CHECK_NUMBER ,  NULL

               CHECK_DATE , GLH.NAME VENDOR_NAME , CBA.ASSET_CODE_COMBINATION_ID

               ACCOUNT_CODE , 'Journal Entry' DESCRIPTION , TO_CHAR

               (NVL(GLV.ENTERED_DR , 0 )  )  RECEIPTS , TO_CHAR (NVL(GLV.ENTERED_CR

               , 0 )  )  PAYMENTS , TO_NUMBER (NVL( NULL  , '0' )  )  PAYMENTS1 ,

               CBA.BANK_ACCOUNT_NAME , CBA.BANK_ACCOUNT_NUM , BANK.PARTY_NAME

               BANK_NAME , BRANCH.PARTY_NAME BRANCH_NAME FROM CE_BANK_ACCOUNTS CBA ,

               GL_CODE_COMBINATIONS_KFV GCC , GL_JE_LINES_V GLV , GL_JE_HEADERS_V

               GLH , HZ_PARTIES BANK , HZ_PARTIES BRANCH   WHERE 1 =  1  AND

               CBA.ASSET_CODE_COMBINATION_ID =  GCC.CODE_COMBINATION_ID  AND

               GLV.SEGMENT5 =  GCC.SEGMENT5  AND GLH.JE_HEADER_ID = 

               GLV.JE_HEADER_ID  AND CBA.BANK_ID =  BANK.PARTY_ID  AND

               CBA.BANK_BRANCH_ID =  BRANCH.PARTY_ID  AND GLH.STATUS =  'P'  AND

               GLH.JE_SOURCE IN ( 'Manual' , 'Spreadsheet' )  AND

               CBA.BANK_ACCOUNT_ID =  :b2  AND GLH.DEFAULT_EFFECTIVE_DATE <  :b1  

               UNION ALL SELECT XDDA.ACTUAL_SETTLEMENT_DATE GL_DATE , TO_CHAR

               (DECODE(SIGN(SUM (XDDA.CASHFLOW_AMOUNT) )  , 1 , 'Receipt' ,

               'Payment' )  )  TYPE , TO_NUMBER (XDDA.DEAL_NUMBER || 

               XDDA.TRANSACTION_NUMBER  ||  XDDA.SETTLEMENT_NUMBER  )  VOUCHER_NO ,

               XDDA.ACTUAL_SETTLEMENT_DATE VOUCHER_DATE ,  NULL  CHECK_NUMBER , 

               NULL  CHECK_DATE , XDDA.CPARTY_CODE VENDOR_NAME ,

               CBA.ASSET_CODE_COMBINATION_ID ACCOUNT_CODE , 'Treasury' DESCRIPTION ,

               TO_CHAR (DECODE(SIGN(SUM (XDDA.CASHFLOW_AMOUNT) )  , 1 , SUM

               (XDDA.CASHFLOW_AMOUNT) , SUM (0) )  )  RECEIPTS , TO_CHAR

               (DECODE(SIGN(SUM (XDDA.CASHFLOW_AMOUNT) )  , -1 , SUM

               (XDDA.CASHFLOW_AMOUNT) *  -1  , SUM (0) )  )  PAYMENTS , TO_NUMBER

               (SUM (XDDA.CASHFLOW_AMOUNT) )  PAYMENTS1 , CBA.BANK_ACCOUNT_NAME

               BANK_ACCOUNT_NAME , CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM ,

               BANK.PARTY_NAME BANK_NAME , BRANCH.PARTY_NAME BRANCH_NAME FROM

               CE_BANK_ACCOUNTS CBA , XTR_DEAL_DATE_AMOUNTS XDDA , HZ_PARTIES BANK ,

               HZ_PARTIES BRANCH   WHERE XDDA.ACCOUNT_NO =  CBA.BANK_ACCOUNT_NUM 

               AND XDDA.SETTLE =  'Y'  AND XDDA.NETOFF_NUMBER IS NULL   AND

               XDDA.STATUS_CODE !=  'CANCELLED'  AND CBA.BANK_ID =  BANK.PARTY_ID 

               AND CBA.BANK_BRANCH_ID =  BRANCH.PARTY_ID  AND

               XDDA.ACTUAL_SETTLEMENT_DATE <  :b1  AND CBA.BANK_ACCOUNT_ID =  :b2  

               GROUP BY XDDA.ACTUAL_SETTLEMENT_DATE , TO_NUMBER (XDDA.DEAL_NUMBER ||

               XDDA.TRANSACTION_NUMBER  ||  XDDA.SETTLEMENT_NUMBER  )  ,

               XDDA.ACTUAL_SETTLEMENT_DATE ,  NULL  ,  NULL  , XDDA.CPARTY_CODE ,

               CBA.ASSET_CODE_COMBINATION_ID , 'Treasury' , CBA.BANK_ACCOUNT_NAME ,

               CBA.BANK_ACCOUNT_NUM , BANK.PARTY_NAME , BRANCH.PARTY_NAME  UNION ALL

               SELECT XDDA.ACTUAL_SETTLEMENT_DATE GL_DATE , 'Payment' TYPE ,

               XDDA.NETOFF_NUMBER VOUCHER_NO , XDDA.ACTUAL_SETTLEMENT_DATE

               VOUCHER_DATE ,  NULL  CHECK_NUMBER ,  NULL  CHECK_DATE ,

               XDDA.CPARTY_CODE VENDOR_NAME , CBA.ASSET_CODE_COMBINATION_ID

               ACCOUNT_CODE , 'Treasury' DESCRIPTION , TO_CHAR (DECODE(SIGN(SUM

               (XDDA.CASHFLOW_AMOUNT) )  , 1 , SUM (XDDA.CASHFLOW_AMOUNT) , SUM (0)

               )  )  RECEIPTS , TO_CHAR (DECODE(SIGN(SUM (XDDA.CASHFLOW_AMOUNT) )  ,

               -1 , SUM (XDDA.CASHFLOW_AMOUNT) *  -1  , SUM (0) )  )  PAYMENTS ,

               TO_NUMBER (SUM (XDDA.CASHFLOW_AMOUNT) )  PAYMENTS1 ,

               CBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME , CBA.BANK_ACCOUNT_NUM

               BANK_ACCOUNT_NUM , BANK.PARTY_NAME BANK_NAME , BRANCH.PARTY_NAME

               BRANCH_NAME FROM CE_BANK_ACCOUNTS CBA , XTR_DEAL_DATE_AMOUNTS XDDA ,

               HZ_PARTIES BANK , HZ_PARTIES BRANCH   WHERE XDDA.ACCOUNT_NO = 

               CBA.BANK_ACCOUNT_NUM  AND XDDA.SETTLE =  'Y'  AND XDDA.NETOFF_NUMBER

               IS NOT NULL   AND CBA.BANK_ID =  BANK.PARTY_ID  AND

               CBA.BANK_BRANCH_ID =  BRANCH.PARTY_ID  AND XDDA.STATUS_CODE != 

               'CANCELLED'  AND XDDA.ACTUAL_SETTLEMENT_DATE <  :b1  AND

               CBA.BANK_ACCOUNT_ID =  :b2   GROUP BY XDDA.ACTUAL_SETTLEMENT_DATE ,

               'Payment' , XDDA.NETOFF_NUMBER , XDDA.ACTUAL_SETTLEMENT_DATE ,  NULL 

               ,  NULL  , XDDA.CPARTY_CODE , CBA.ASSET_CODE_COMBINATION_ID ,

               'Treasury' , CBA.BANK_ACCOUNT_NAME , CBA.BANK_ACCOUNT_NUM ,

               BANK.PARTY_NAME , BRANCH.PARTY_NAME  ORDER BY 1 )  

         Rationale

            The SQL spent 99% of its database time on CPU, I/O and Cluster waits.

            This part of database time may be improved by the SQL Tuning Advisor.

         Rationale

            Database time for this SQL was divided as follows: 100% for SQL

            execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

            execution.

         Rationale

            SQL statement with SQL_ID "gy02p15c6bf0w" was executed 33 times and had

            an average elapsed time of 526 seconds.

         Rationale

            At least 2 distinct execution plans were utilized for this SQL statement

            during the analysis period.

       

         Recommendation 5: SQL Tuning

         Estimated benefit is .15 active sessions, 3.03% of total activity.

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

         Action

            Run SQL Tuning Advisor on the SELECT statement with SQL_ID

            "57wrbkkh3svfd".

            Related Object

               SQL statement with SQL_ID 57wrbkkh3svfd.

               SELECT SUM(AMOUNT_APPLIED) FROM AR_RECEIVABLE_APPLICATIONS WHERE

               APPLIED_PAYMENT_SCHEDULE_ID = -8 AND STATUS = 'ACTIVITY' AND

               NVL(CONFIRMED_FLAG,'Y') = 'Y' AND CUSTOMER_TRX_ID = :B1

         Rationale

            The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

            This part of database time may be improved by the SQL Tuning Advisor.

         Rationale

            Database time for this SQL was divided as follows: 100% for SQL

            execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

            execution.

         Rationale

            SQL statement with SQL_ID "57wrbkkh3svfd" was executed 3971 times and

            had an average elapsed time of 3.3 seconds.

         Rationale

            Top level calls to execute the PL/SQL statement with SQL_ID

            "d2fp79p75ycjv" are responsible for 100% of the database time spent on

            the SELECT statement with SQL_ID "57wrbkkh3svfd".

            Related Object

               SQL statement with SQL_ID d2fp79p75ycjv.

               BEGIN xxsd_cm_application_refund(:errbuf,:rc,:A0,:A1,:A2,:A3,:A4,:A5)

               ; END;

       

         Recommendation 6: SQL Tuning

         Estimated benefit is .11 active sessions, 2.22% of total activity.

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

         Action

            Run SQL Tuning Advisor on the SELECT statement with SQL_ID

            "f6wqn8h38mktq".

            Related Object

               SQL statement with SQL_ID f6wqn8h38mktq.

               /* SQL Analyze(1) */ select /*+  full(t)    parallel(t,8)

               parallel_index(t,8) dbms_stats cursor_sharing_exact

               use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad 

               */to_char(count("PRODUCT_FISC_CLASSIFICATION")),to_char(substrb(dump(

               min("PRODUCT_FISC_CLASSIFICATION"),16,0,32),1,120)),to_char(substrb(d

               ump(max("PRODUCT_FISC_CLASSIFICATION"),16,0,32),1,120)),to_char(count

               ("PRODUCT_CATEGORY")),to_char(substrb(dump(min("PRODUCT_CATEGORY"),16

               ,0,32),1,120)),to_char(substrb(dump(max("PRODUCT_CATEGORY"),16,0,32),

               1,120)),to_char(count("PRODUCT_TYPE")),to_char(substrb(dump(min("PROD

               UCT_TYPE"),16,0,32),1,120)),to_char(substrb(dump(max("PRODUCT_TYPE"),

               16,0,32),1,120)),to_char(count("LINE_INTENDED_USE")),to_char(substrb(

               dump(min("LINE_INTENDED_USE"),16,0,32),1,120)),to_char(substrb(dump(m

               ax("LINE_INTENDED_USE"),16,0,32),1,120)),to_char(count("DOCUMENT_NUMB

               ER")),to_char(substrb(dump(min("DOCUMENT_NUMBER"),16,0,32),1,120)),to

               _char(substrb(dump(max("DOCUMENT_NUMBER"),16,0,32),1,120)),to_char(co

               unt("TRX_NUMBER")),to_char(substrb(dump(min("TRX_NUMBER"),16,0,32),1,

               120)),to_char(substrb(dump(max("TRX_NUMBER"),16,0,32),1,120)),to_char

               (count("LINE_NUMBER")),to_char(substrb(dump(min("LINE_NUMBER"),16,0,3

               2),1,120)),to_char(substrb(dump(max("LINE_NUMBER"),16,0,32),1,120)),t

               o_char(count("QUANTITY")),to_char(substrb(dump(min("QUANTITY"),16,0,3

               2),1,120)),to_char(substrb(dump(max("QUANTITY"),16,0,32),1,120)),to_c

               har(count("QUANTITY_ORDERED")),to_char(substrb(dump(min("QUANTITY_ORD

               ERED"),16,0,32),1,120)),to_char(substrb(dump(max("QUANTITY_ORDERED"),

               16,0,32),1,120)),to_char(count("UNIT_SELLING_PRICE")),to_char(substrb

               (dump(min("UNIT_SELLING_PRICE"),16,0,32),1,120)),to_char(substrb(dump

               (max("UNIT_SELLING_PRICE"),16,0,32),1,120)),to_char(count("UNIT_STAND

               ARD_PRICE")),to_char(substrb(dump(min("UNIT_STANDARD_PRICE"),16,0,32)

               ,1,120)),to_char(substrb(dump(max("UNIT_STANDARD_PRICE"),16,0,32),1,1

               20)),to_char(count("PRINTING_OPTION")),to_char(substrb(dump(min("PRIN

               TING_OPTION"),16,0,32),1,120)),to_char(substrb(dump(max("PRINTING_OPT

               ION"),16,0,32),1,120)),to_char(count("INTERFACE_STATUS")),to_char(sub

               strb(dump(min("INTERFACE_STATUS"),16,0,32),1,120)),to_char(substrb(du

               mp(max("INTERFACE_STATUS"),16,0,32),1,120)),to_char(count("REQUEST_ID

               ")),to_char(substrb(dump(min("REQUEST_ID"),16,0,32),1,120)),to_char(s

               ubstrb(dump(max("REQUEST_ID"),16,0,32),1,120)),to_char(count("RELATED

               _BATCH_SOURCE_NAME")),to_char(substrb(dump(min("RELATED_BATCH_SOURCE_

               NAME"),16,0,32),1,120)),to_char(substrb(dump(max("RELATED_BATCH_SOURC

               E_NAME"),16,0,32),1,120)),to_char(count("RELATED_TRX_NUMBER")),to_cha

               r(substrb(dump(min("RELATED_TRX_NUMBER"),16,0,32),1,120)),to_char(sub

               strb(dump(max("RELATED_TRX_NUMBER"),16,0,32),1,120)),to_char(count("R

               ELATED_CUSTOMER_TRX_ID")),to_char(substrb(dump(min("RELATED_CUSTOMER_

               TRX_ID"),16,0,32),1,120)),to_char(substrb(dump(max("RELATED_CUSTOMER_

               TRX_ID"),16,0,32),1,120)),to_char(count("PREVIOUS_CUSTOMER_TRX_ID")),

               to_char(substrb(dump(min("PREVIOUS_CUSTOMER_TRX_ID"),16,0,32),1,120))

               ,to_char(substrb(dump(max("PREVIOUS_CUSTOMER_TRX_ID"),16,0,32),1,120)

               ),to_char(count("CREDIT_METHOD_FOR_ACCT_RULE")),to_char(substrb(dump(

               min("CREDIT_METHOD_FOR_ACCT_RULE"),16,0,32),1,120)),to_char(substrb(d

               ump(max("CREDIT_METHOD_FOR_ACCT_RULE"),16,0,32),1,120)),to_char(count

               ("CREDIT_METHOD_FOR_INSTALLMENTS")),to_char(substrb(dump(min("CREDIT_

               METHOD_FOR_INSTALLMENTS"),16,0,32),1,120)),to_char(substrb(dump(max("

               CREDIT_METHOD_FOR_INSTALLMENTS"),16,0,32),1,120)),to_char(count("REAS

               ON_CODE")),to_char(substrb(dump(min("REASON_CODE"),16,0,32),1,120)),t

               o_char(substrb(dump(max("REASON_CODE"),16,0,32),1,120)),to_char(count

               ("TAX_RATE")),to_char(substrb(dump(min("TAX_RATE"),16,0,32),1,120)),t

               o_char(substrb(dump(max("TAX_RATE"),16,0,32),1,120)),to_char(count("T

               AX_CODE")),to_char(substrb(dump(min("TAX_CODE"),16,0,32),1,120)),to_c

               har(substrb(dump(max("TAX_CODE"),16,0,32),1,120)),to_char(count("TAX_

               PRECEDENCE")),to_char(substrb(dump(min("TAX_PRECEDENCE"),16,0,32),1,1

               20)),to_char(substrb(dump(max("TAX_PRECEDENCE"),16,0,32),1,120)),to_c

               har(count("EXCEPTION_ID")),to_char(substrb(dump(min("EXCEPTION_ID"),1

               6,0,32),1,120)),to_char(substrb(dump(max("EXCEPTION_ID"),16,0,32),1,1

               20)),to_char(count("EXEMPTION_ID")),to_char(substrb(dump(min("EXEMPTI

               ON_ID"),16,0,32),1,120)),to_char(substrb(dump(max("EXEMPTION_ID"),16,

               0,32),1,120)),to_char(count("SHIP_DATE_ACTUAL")),to_char(substrb(dump

               (min("SHIP_DATE_ACTUAL"),16,0,32),1,120)),to_char(substrb(dump(max("S

               HIP_DATE_ACTUAL"),16,0,32),1,120)),to_char(count("FOB_POINT")),to_cha

               r(substrb(dump(min("FOB_POINT"),16,0,32),1,120)),to_char(substrb(dump

               (max("FOB_POINT"),16,0,32),1,120)),to_char(count("SHIP_VIA")),to_char

               (substrb(dump(min("SHIP_VIA"),16,0,32),1,120)),to_char(substrb(dump(m

               ax("SHIP_VIA"),16,0,32),1,120)),to_char(count("WAYBILL_NUMBER")),to_c

               har(substrb(dump(min("WAYBILL_NUMBER"),16,0,32),1,120)),to_char(subst

               rb(dump(max("WAYBILL_NUMBER"),16,0,32),1,120)),to_char(count("INVOICI

               NG_RULE_NAME")),to_char(substrb(dump(min("INVOICING_RULE_NAME"),16,0,

               32),1,120)),to_char(substrb(dump(max("INVOICING_RULE_NAME"),16,0,32),

               1,120)),to_char(count("INVOICING_RULE_ID")),to_char(substrb(dump(min(

               "INVOICING_RULE_ID"),16,0,32),1,120)),to_char(substrb(dump(max("INVOI

               CING_RULE_ID"),16,0,32),1,120)),to_char(count("ACCOUNTING_RULE_NAME")

               ),to_char(substrb(dump(min("ACCOUNTING_RULE_NAME"),16,0,32),1,120)),t

               o_char(substrb(dump(max("ACCOUNTING_RULE_NAME"),16,0,32),1,120)),to_c

               har(count("ACCOUNTING_RULE_ID")),to_char(substrb(dump(min("ACCOUNTING

               _RULE_ID"),16,0,32),1,120)),to_char(substrb(dump(max("ACCOUNTING_RULE

               _ID"),16,0,32),1,120)),to_char(count("ACCOUNTING_RULE_DURATION")),to_

               char(substrb(dump(min("ACCOUNTING_RULE_DURATION"),16,0,32),1,120)),to

               _char(substrb(dump(max("ACCOUNTING_RULE_DURATION"),16,0,32),1,120)),t

               o_char(count("RULE_START_DATE")),to_char(substrb(dump(min("RULE_START

               _DATE"),16,0,32),1,120)),to_char(substrb(dump(max("RULE_START_DATE"),

               16,0,32),1,120)),to_char(count("PRIMARY_SALESREP_NUMBER")),to_char(su

               bstrb(dump(min("PRIMARY_SALESREP_NUMBER"),16,0,32),1,120)),to_char(su

               bstrb(dump(max("PRIMARY_SALESREP_NUMBER"),16,0,32),1,120)),to_char(co

               unt("PRIMARY_SALESREP_ID")),to_char(substrb(dump(min("PRIMARY_SALESRE

               P_ID"),16,0,32),1,120)),to_char(substrb(dump(max("PRIMARY_SALESREP_ID

               "),16,0,32),1,120)),to_char(count("SALES_ORDER")),to_char(substrb(dum

               p(min("SALES_ORDER"),16,0,32),1,120)),to_char(substrb(dump(max("SALES

               _ORDER"),16,0,32),1,120)),to_char(count("SALES_ORDER_LINE")),to_char(

               substrb(dump(min("SALES_ORDER_LINE"),16,0,32),1,120)),to_char(substrb

               (dump(max("SALES_ORDER_LINE"),16,0,32),1,120)),to_char(count("SALES_O

               RDER_DATE")),to_char(substrb(dump(min("SALES_ORDER_DATE"),16,0,32),1,

               120)),to_char(substrb(dump(max("SALES_ORDER_DATE"),16,0,32),1,120)),t

               o_char(count("SALES_ORDER_SOURCE")),to_char(substrb(dump(min("SALES_O

               RDER_SOURCE"),16,0,32),1,120)),to_char(substrb(dump(max("SALES_ORDER_

               SOURCE"),16,0,32),1,120)),to_char(count("SALES_ORDER_REVISION")),to_c

               har(substrb(dump(min("SALES_ORDER_REVISION"),16,0,32),1,120)),to_char

               (substrb(dump(max("SALES_ORDER_REVISION"),16,0,32),1,120)),to_char(co

               unt("PURCHASE_ORDER")),to_char(substrb(dump(min("PURCHASE_ORDER"),16,

               0,32),1,120)),to_char(substrb(dump(max("PURCHASE_ORDER"),16,0,32),1,1

               20)),to_char(count("PURCHASE_ORDER_REVISION")),to_char(substrb(dump(m

               in("PURCHASE_ORDER_REVISION"),16,0,32),1,120)),to_char(substrb(dump(m

               ax("PURCHASE_ORDER_REVISION"),16,0,32),1,120)),to_char(count("PURCHAS

               E_ORDER_DATE")),to_char(substrb(dump(min("PURCHASE_ORDER_DATE"),16,0,

               32),1,120)),to_char(substrb(dump(max("PURCHASE_ORDER_DATE"),16,0,32),

               1,120)),to_char(count("AGREEMENT_NAME")),to_char(substrb(dump(min("AG

               REEMENT_NAME"),16,0,32),1,120)),to_char(substrb(dump(max("AGREEMENT_N

               AME"),16,0,32),1,120)),to_char(count("AGREEMENT_ID")),to_char(substrb

               (dump(min("AGREEMENT_ID"),16,0,32),1,120)),to_char(substrb(dump(max("

               AGREEMENT_ID"),16,0,32),1,120)),to_char(count("MEMO_LINE_NAME")),to_c

               har(substrb(dump(min("MEMO_LINE_NAME"),16,0,32),1,120)),to_char(subst

               rb(dump(max("MEMO_LINE_NAME"),16,0,32),1,120)),to_char(count("MEMO_LI

               NE_ID")),to_char(substrb(dump(min("MEMO_LINE_ID"),16,0,32),1,120)),to

               _char(substrb(dump(max("MEMO_LINE_ID"),16,0,32),1,120)),to_char(count

               ("INVENTORY_ITEM_ID")),to_char(substrb(dump(min("INVENTORY_ITEM_ID"),

               16,0,32),1,120)),to_char(substrb(dump(max("INVENTORY_ITEM_ID"),16,0,3

               2),1,120)),to_char(count("MTL_SYSTEM_ITEMS_SEG1")),to_char(substrb(du

               mp(min("MTL_SYSTEM_ITEMS_SEG1"),16,0,32),1,120)),to_char(substrb(dump

               (max("MTL_SYSTEM_ITEMS_SEG1"),16,0,32),1,120)),to_char(count("MTL_SYS

               TEM_ITEMS_SEG2")),to_char(substrb(dump(min("MTL_SYSTEM_ITEMS_SEG2"),1

               6,0,32),1,120)),to_char(substrb(dump(max("MTL_SYSTEM_ITEMS_SEG2"),16,

               0,32),1,120)),to_char(count("MTL_SYSTEM_ITEMS_SEG3")),to_char(substrb

               (dump(min("MTL_SYSTEM_ITEMS_SEG3"),16,0,32),1,120)),to_char(substrb(d

               ump(max("MTL_SYSTEM_ITEMS_SEG3"),16,0,32),1,120)),to_char(count("MTL_

               SYSTEM_ITEMS_SEG4")),to_char(substrb(dump(min("MTL_SYSTEM_ITEMS_SEG4"

               ),16,0,32),1,120)),to_char(substrb(dump(max("MTL_SYSTEM_ITEMS_SEG4"),

               16,0,32),1,120)),to_char(count("MTL_SYSTEM_ITEMS_SEG5")),to_char(subs

               trb(dump(min("MTL_SYSTEM_ITEMS_SEG5"),16,0,32),1,120)),to_char(substr

               b(dump(max("MTL_SYSTEM_ITEMS_SEG5"),16,0,32),1,120)),to_char(count("M

               TL_SYSTEM_ITEMS_SEG6")),to_char(substrb(dump(min("MTL_SYSTEM_ITEMS_SE

               G6"),16,0,32),1,120)),to_char(substrb(dump(max("MTL_SYSTEM_ITEMS_SEG6

               "),16,0,32),1,120)),to_char(count("MTL_SYSTEM_ITEMS_SEG7")),to_char(s

               ubstrb(dump(min("MTL_SYSTEM_ITEMS_SEG7"),16,0,32),1,120)),to_char(sub

               strb(dump(max("MTL_SYSTEM_ITEMS_SEG7"),16,0,32),1,120)),to_char(count

               ("MTL_SYSTEM_ITEMS_SEG8")),to_char(substrb(dump(min("MTL_SYSTEM_ITEMS

               _SEG8"),16,0,32),1,120)),to_char(substrb(dump(max("MTL_SYSTEM_ITEMS_S

               EG8"),16,0,32),1,120)),to_char(count("MTL_SYSTEM_ITEMS_SEG9")),to_cha

               r(substrb(dump(min("MTL_SYSTEM_ITEMS_SEG9"),16,0,32),1,120)),to_char(

               substrb(dump(max("MTL_SYSTEM_ITEMS_SEG9"),16,0,32),1,120)),to_char(co

               unt("MTL_SYSTEM_ITEMS_SEG10")),to_char(substrb(dump(min("MTL_SYSTEM_I

               TEMS_SEG10"),16,0,32),1,120)),to_char(substrb(dump(max("MTL_SYSTEM_IT

               EMS_SEG10"),16,0,32),1,120)),to_char(count("MTL_SYSTEM_ITEMS_SEG11"))

               ,to_char(substrb(dump(min("MTL_SYSTEM_ITEMS_SEG11"),16,0,32),1,120)),

               to_char(substrb(dump(max("MTL_SYSTEM_ITEMS_SEG11"),16,0,32),1,120)),t

               o_char(count("MTL_SYSTEM_ITEMS_SEG12")),to_char(substrb(dump(min("MTL

               _SYSTEM_ITEMS_SEG12"),16,0,32),1,120)),to_char(substrb(dump(max("MTL_

               SYSTEM_ITEMS_SEG12"),16,0,32),1,120)),to_char(count("MTL_SYSTEM_ITEMS

               _SEG13")),to_char(substrb(dump(min("MTL_SYSTEM_ITEMS_SEG13"),16,0,32)

               ,1,120)),to_char(substrb(dump(max("MTL_SYSTEM_ITEMS_SEG13"),16,0,32),

               1,120)),to_char(count("MTL_SYSTEM_ITEMS_SEG14")),to_char(substrb(dump

               (min("MTL_SYSTEM_ITEMS_SEG14"),16,0,32),1,120)),to_char(substrb(dump(

               max("MTL_SYSTEM_ITEMS_SEG14"),16,0,32),1,120)),to_char(count("MTL_SYS

               TEM_ITEMS_SEG15")),to_char(substrb(dump(min("MTL_SYSTEM_ITEMS_SEG15")

               ,16,0,32),1,120)),to_char(substrb(dump(max("MTL_SYSTEM_ITEMS_SEG15"),

               16,0,32),1,120)),to_char(count("MTL_SYSTEM_ITEMS_SEG16")),to_char(sub

               strb(dump(min("MTL_SYSTEM_ITEMS_SEG16"),16,0,32),1,120)),to_char(subs

               trb(dump(max("MTL_SYSTEM_ITEMS_SEG16"),16,0,32),1,120)),to_char(count

               ("MTL_SYSTEM_ITEMS_SEG17")),to_char(substrb(dump(min("MTL_SYSTEM_ITEM

               S_SEG17"),16,0,32),1,120)),to_char(substrb(dump(max("MTL_SYSTEM_ITEMS

               _SEG17"),16,0,32),1,120)),to_char(count("MTL_SYSTEM_ITEMS_SEG18")),to

               _char(substrb(dump(min("MTL_SYSTEM_ITEMS_SEG18"),16,0,32),1,120)),to_

               char(substrb(dump(max("MTL_SYSTEM_ITEMS_SEG18"),16,0,32),1,120)),to_c

               har(count("MTL_SYSTEM_ITEMS_SEG19")),to_char(substrb(dump(min("MTL_SY

               STEM_ITEMS_SEG19"),16,0,32),1,120)),to_char(substrb(dump(max("MTL_SYS

               TEM_ITEMS_SEG19"),16,0,32),1,120)),to_char(count("MTL_SYSTEM_ITEMS_SE

               G20")),to_char(substrb(dump(min("MTL_SYSTEM_ITEMS_SEG20"),16,0,32),1,

               120)),to_char(substrb(dump(max("MTL_SYSTEM_ITEMS_SEG20"),16,0,32),1,1

               20)),to_char(count("REFERENCE_LINE_ID")),to_char(substrb(dump(min("RE

               FERENCE_LINE_ID"),16,0,32),1,120)),to_char(substrb(dump(max("REFERENC

               E_LINE_ID"),16,0,32),1,120)),to_char(count("REFERENCE_LINE_CONTEXT"))

               ,to_char(substrb(dump(min("REFERENCE_LINE_CONTEXT"),16,0,32),1,120)),

               to_char(substrb(dump(max("REFERENCE_LINE_CONTEXT"),16,0,32),1,120)),t

               o_char(count("REFERENCE_LINE_ATTRIBUTE1")),to_char(substrb(dump(min("

               REFERENCE_LINE_ATTRIBUTE1"),16,0,32),1,120)),to_char(substrb(dump(max

               ("REFERENCE_LINE_ATTRIBUTE1"),16,0,32),1,120)),to_char(count("REFEREN

               CE_LINE_ATTRIBUTE2")),to_char(substrb(dump(min("REFERENCE_LINE_ATTRIB

               UTE2"),16,0,32),1,120)),to_char(substrb(dump(max("REFERENCE_LINE_ATTR

               IBUTE2"),16,0,32),1,120)),to_char(count("REFERENCE_LINE_ATTRIBUTE3"))

               ,to_char(substrb(dump(min("REFERENCE_LINE_ATTRIBUTE3"),16,0,32),1,120

               )),to_char(substrb(dump(max("REFERENCE_LINE_ATTRIBUTE3"),16,0,32),1,1

               20)),to_char(count("REFERENCE_LINE_ATTRIBUTE4")),to_char(substrb(dump

               (min("REFERENCE_LINE_ATTRIBUTE4"),16,0,32),1,120)),to_char(substrb(du

               mp(max("REFERENCE_LINE_ATTRIBUTE4"),16,0,32),1,120)),to_char(count("R

               EFERENCE_LINE_ATTRIBUTE5")),to_char(substrb(dump(min("REFERENCE_LINE_

               ATTRIBUTE5"),16,0,32),1,120)),to_char(substrb(dump(max("REFERENCE_LIN

               E_ATTRIBUTE5"),16,0,32),1,120)),to_char(count("REFERENCE_LINE_ATTRIBU

               TE6")),to_char(substrb(dump(min("REFERENCE_LINE_ATTRIBUTE6"),16,0,32)

               ,1,120)),to_char(substrb(dump(max("REFERENCE_LINE_ATTRIBUTE6"),16,0,3

               2),1,120)),to_char(count("REFERENCE_LINE_ATTRIBUTE7")),to_char(substr

               b(dump(min("REFERENCE_LINE_ATTRIBUTE7"),16,0,32),1,120)),to_char(subs

               trb(dump(max("REFERENCE_LINE_ATTRIBUTE7"),16,0,32),1,120)),to_char(co

               unt("TERRITORY_ID")),to_char(substrb(dump(min("TERRITORY_ID"),16,0,32

               ),1,120)),to_char(substrb(dump(max("TERRITORY_ID"),16,0,32),1,120)),t

               o_char(count("TERRITORY_SEGMENT1")),to_char(substrb(dump(min("TERRITO

               RY_SEGMENT1"),16,0,32),1,120)),to_char(substrb(dump(max("TERRITORY_SE

               GMENT1"),16,0,32),1,120)),to_char(count("TERRITORY_SEGMENT2")),to_cha

               r(substrb(dump(min("TERRITORY_SEGMENT2"),16,0,32),1,120)),to_char(sub

               strb(dump(max("TERRITORY_SEGMENT2"),16,0,32),1,120)),to_char(count("T

               ERRITORY_SEGMENT3")),to_char(substrb(dump(min("TERRITORY_SEGMENT3"),1

               6,0,32),1,120)),to_char(substrb(dump(max("TERRITORY_SEGMENT3"),16,0,3

               2),1,120)),to_char(count("TERRITORY_SEGMENT4")),to_char(substrb(dump(

               min("TERRITORY_SEGMENT4"),16,0,32),1,120)),to_char(substrb(dump(max("

               TERRITORY_SEGMENT4"),16,0,32),1,120)),to_char(count("TERRITORY_SEGMEN

               T5")),to_char(substrb(dump(min("TERRITORY_SEGMENT5"),16,0,32),1,120))

               ,to_char(substrb(dump(max("TERRITORY_SEGMENT5"),16,0,32),1,120)),to_c

               har(count("TERRITORY_SEGMENT6")),to_char(substrb(dump(min("TERRITORY_

               SEGMENT6"),16,0,32),1,120)),to_char(substrb(dump(max("TERRITORY_SEGME

               NT6"),16,0,32),1,120)),to_char(count("TERRITORY_SEGMENT7")),to_char(s

               ubstrb(dump(min("TERRITORY_SEGMENT7"),16,0,32),1,120)),to_char(substr

               b(dump(max("TERRITORY_SEGMENT7"),16,0,32),1,120)),to_char(count("TERR

               ITORY_SEGMENT8")),to_char(substrb(dump(min("TERRITORY_SEGMENT8"),16,0

               ,32),1,120)),to_char(substrb(dump(max("TERRITORY_SEGMENT8"),16,0,32),

               1,120)),to_char(count("TERRITORY_SEGMENT9")),to_char(substrb(dump(min

               ("TERRITORY_SEGMENT9"),16,0,32),1,120)),to_char(substrb(dump(max("TER

               RITORY_SEGMENT9"),16,0,32),1,120)),to_char(count("TERRITORY_SEGMENT10

               ")),to_char(substrb(dump(min("TERRITORY_SEGMENT10"),16,0,32),1,120)),

               to_char(substrb(dump(max("TERRITORY_SEGMENT10"),16,0,32),1,120)),to_c

               har(count("TERRITORY_SEGMENT11")),to_char(substrb(dump(min("TERRITORY

               _SEGMENT11"),16,0,32),1,120)),to_char(substrb(dump(max("TERRITORY_SEG

               MENT11"),16,0,32),1,120)),to_char(count("TERRITORY_SEGMENT12")),to_ch

               ar(substrb(dump(min("TERRITORY_SEGMENT12"),16,0,32),1,120)),to_char(s

               ubstrb(dump(max("TERRITORY_SEGMENT12"),16,0,32),1,120)),to_char(count

               ("TERRITORY_SEGMENT13")),to_char(substrb(dump(min("TERRITORY_SEGMENT1

               3"),16,0,32),1,120)),to_char(substrb(dump(max("TERRITORY_SEGMENT13"),

               16,0,32),1,120)),to_char(count("TERRITORY_SEGMENT14")),to_char(substr

               b(dump(min("TERRITORY_SEGMENT14"),16,0,32),1,120)),to_char(substrb(du

               mp(max("TERRITORY_SEGMENT14"),16,0,32),1,120)),to_char(count("TERRITO

               RY_SEGMENT15")),to_char(substrb(dump(min("TERRITORY_SEGMENT15"),16,0,

               32),1,120)),to_char(substrb(dump(max("TERRITORY_SEGMENT15"),16,0,32),

               1,120)),to_char(count("TERRITORY_SEGMENT16")),to_char(substrb(dump(mi

               n("TERRITORY_SEGMENT16"),16,0,32),1,120)),to_char(substrb(dump(max("T

               ERRITORY_SEGMENT16"),16,0,32),1,120)),to_char(count("TERRITORY_SEGMEN

               T17")),to_char(substrb(dump(min("TERRITORY_SEGMENT17"),16,0,32),1,120

               )),to_char(substrb(dump(max("TERRITORY_SEGMENT17"),16,0,32),1,120)),t

               o_char(count("TERRITORY_SEGMENT18")),to_char(substrb(dump(min("TERRIT

               ORY_SEGMENT18"),16,0,32),1,120)),to_char(substrb(dump(max("TERRITORY_

               SEGMENT18"),16,0,32),1,120)),to_char(count("TERRITORY_SEGMENT19")),to

               _char(substrb(dump(min("TERRITORY_SEGMENT19"),16,0,32),1,120)),to_cha

               r(substrb(dump(max("TERRITORY_SEGMENT19"),16,0,32),1,120)),to_char(co

               unt("TERRITORY_SEGMENT20")),to_char(substrb(dump(min("TERRITORY_SEGME

               NT20"),16,0,32),1,120)),to_char(substrb(dump(max("TERRITORY_SEGMENT20

               "),16,0,32),1,120)),to_char(count("ATTRIBUTE_CATEGORY")),to_char(subs

               trb(dump(min("ATTRIBUTE_CATEGORY"),16,0,32),1,120)),to_char(substrb(d

               ump(max("ATTRIBUTE_CATEGORY"),16,0,32),1,120)),to_char(count("ATTRIBU

               TE1")),to_char(substrb(dump(min("ATTRIBUTE1"),16,0,32),1,120)),to_cha

               r(substrb(dump(max("ATTRIBUTE1"),16,0,32),1,120)),to_char(count("ATTR

               IBUTE2")),to_char(substrb(dump(min("ATTRIBUTE2"),16,0,32),1,120)),to_

               char(substrb(dump(max("ATTRIBUTE2"),16,0,32),1,120)),to_char(count("A

               TTRIBUTE3")),to_char(substrb(dump(min("ATTRIBUTE3"),16,0,32),1,120)),

               to_char(substrb(dump(max("ATTRIBUTE3"),16,0,32),1,120)),to_char(count

               ("ATTRIBUTE4")),to_char(substrb(dump(min("ATTRIBUTE4"),16,0,32),1,120

               )),to_char(substrb(dump(max("ATTRIBUTE4"),16,0,32),1,120)),to_char(co

               unt("ATTRIBUTE5")),to_char(substrb(dump(min("ATTRIBUTE5"),16,0,32),1,

               120)),to_char(substrb(dump(max("ATTRIBUTE5"),16,0,32),1,120)),to_char

               (count("ATTRIBUTE6")),to_char(substrb(dump(min("ATTRIBUTE6"),16,0,32)

               ,1,120)),to_char(substrb(dump(max("ATTRIBUTE6"),16,0,32),1,120)),to_c

               har(count("ATTRIBUTE7")),to_char(substrb(dump(min("ATTRIBUTE7"),16,0,

               32),1,120)),to_char(substrb(dump(max("ATTRIBUTE7"),16,0,32),1,120)),t

               o_char(count("ATTRIBUTE8")),to_char(substrb(dump(min("ATTRIBUTE8"),16

               ,0,32),1,120)),to_char(substrb(dump(max("ATTRIBUTE8"),16,0,32),1,120)

               ),to_char(count("ATTRIBUTE9")),to_char(substrb(dump(min("ATTRIBUTE9")

               ,16,0,32),1,120)),to_char(substrb(dump(max("ATTRIBUTE9"),16,0,32),1,1

               20)),to_char(count("ATTRIBUTE10")),to_char(substrb(dump(min("ATTRIBUT

               E10"),16,0,32),1,120)),to_char(substrb(dump(max("ATTRIBUTE10"),16,0,3

               2),1,120)),to_char(count("ATTRIBUTE11")),to_char(substrb(dump(min("AT

               TRIBUTE11"),16,0,32),1,120)),to_char(substrb(dump(max("ATTRIBUTE11"),

               16,0,32),1,120)),to_char(count("ATTRIBUTE12")),to_char(substrb(dump(m

               in("ATTRIBUTE12"),16,0,32),1,120)),to_char(substrb(dump(max("ATTRIBUT

               E12"),16,0,32),1,120)),to_char(count("ATTRIBUTE13")),to_char(substrb(

               dump(min("ATTRIBUTE13"),16,0,32),1,120)),to_char(substrb(dump(max("AT

               TRIBUTE13"),16,0,32),1,120)),to_char(count("ATTRIBUTE14")),to_char(su

               bstrb(dump(min("ATTRIBUTE14"),16,0,32),1,120)),to_char(substrb(dump(m

               ax("ATTRIBUTE14"),16,0,32),1,120)),to_char(count("ATTRIBUTE15")),to_c

               har(substrb(dump(min("ATTRIBUTE15"),16,0,32),1,120)),to_char(substrb(

               dump(max("ATTRIBUTE15"),16,0,32),1,120)),to_char(count("HEADER_ATTRIB

               UTE_CATEGORY")),to_char(substrb(dump(min("HEADER_ATTRIBUTE_CATEGORY")

               ,16,0,32),1,120)),to_char(substrb(dump(max("HEADER_ATTRIBUTE_CATEGORY

               "),16,0,32),1,120)),to_char(count("TAXED_UPSTREAM_FLAG")),to_char(sub

               strb(dump(min("TAXED_UPSTREAM_FLAG"),16,0,32),1,120)),to_char(substrb

               (dump(max("TAXED_UPSTREAM_FLAG"),16,0,32),1,120)),to_char(count("ASSE

               SSABLE_VALUE")),to_char(substrb(dump(min("ASSESSABLE_VALUE"),16,0,32)

               ,1,120)),to_char(substrb(dump(max("ASSESSABLE_VALUE"),16,0,32),1,120)

               ),to_char(count("SOURCE_EVENT_CLASS_CODE")),to_char(substrb(dump(min(

               "SOURCE_EVENT_CLASS_CODE"),16,0,32),1,120)),to_char(substrb(dump(max(

               "SOURCE_EVENT_CLASS_CODE"),16,0,32),1,120)),to_char(count("SOURCE_ENT

               ITY_CODE")),to_char(substrb(dump(min("SOURCE_ENTITY_CODE"),16,0,32),1

               ,120)),to_char(substrb(dump(max("SOURCE_ENTITY_CODE"),16,0,32),1,120)

               ),to_char(count("SOURCE_TRX_ID")),to_char(substrb(dump(min("SOURCE_TR

               X_ID"),16,0,32),1,120)),to_char(substrb(dump(max("SOURCE_TRX_ID"),16,

               0,32),1,120)),to_char(count("SOURCE_TRX_LINE_ID")),to_char(substrb(du

               mp(min("SOURCE_TRX_LINE_ID"),16,0,32),1,120)),to_char(substrb(dump(ma

               x("SOURCE_TRX_LINE_ID"),16,0,32),1,120)),to_char(count("SOURCE_TRX_LI

               NE_TYPE")),to_char(substrb(dump(min("SOURCE_TRX_LINE_TYPE"),16,0,32),

               1,120)),to_char(substrb(dump(max("SOURCE_TRX_LINE_TYPE"),16,0,32),1,1

               20)),to_char(count("SOURCE_TRX_DETAIL_TAX_LINE_ID")),to_char(substrb(

               dump(min("SOURCE_TRX_DETAIL_TAX_LINE_ID"),16,0,32),1,120)),to_char(su

               bstrb(dump(max("SOURCE_TRX_DETAIL_TAX_LINE_ID"),16,0,32),1,120)),to_c

               har(count("HISTORICAL_FLAG")),to_char(substrb(dump(min("HISTORICAL_FL

               AG"),16,0,32),1,120)),to_char(substrb(dump(max("HISTORICAL_FLAG"),16,

               0,32),1,120)),to_char(count("TAX_REGIME_CODE")),to_char(substrb(dump(

               min("TAX_REGIME_CODE"),16,0,32),1,120)),to_char(substrb(dump(max("TAX

               _REGIME_CODE"),16,0,32),1,120)),to_char(count("TAX")),to_char(substrb

               (dump(min("TAX"),16,0,32),1,120)),to_char(substrb(dump(max("TAX"),16,

               0,32),1,120)),to_char(count("TAX_STATUS_CODE")),to_char(substrb(dump(

               min("TAX_STATUS_CODE"),16,0,32),1,120)),to_char(substrb(dump(max("TAX

               _STATUS_CODE"),16,0,32),1,120)),to_char(count("TAX_RATE_CODE")),to_ch

               ar(substrb(dump(min("TAX_RATE_CODE"),16,0,32),1,120)),to_char(substrb

               (dump(max("TAX_RATE_CODE"),16,0,32),1,120)),to_char(count("ORIG_SYSTE

               M_BATCH_NAME")),to_char(substrb(dump(min("ORIG_SYSTEM_BATCH_NAME"),16

               ,0,32),1,120)),to_char(substrb(dump(max("ORIG_SYSTEM_BATCH_NAME"),16,

               0,32),1,120)),to_char(count("LINK_TO_LINE_ATTRIBUTE15")),to_char(subs

               trb(dump(min("LINK_TO_LINE_ATTRIBUTE15"),16,0,32),1,120)),to_char(sub

               strb(dump(max("LINK_TO_LINE_ATTRIBUTE15"),16,0,32),1,120)),to_char(co

               unt("TAX_JURISDICTION_CODE")),to_char(substrb(dump(min("TAX_JURISDICT

               ION_CODE"),16,0,32),1,120)),to_char(substrb(dump(max("TAX_JURISDICTIO

               N_CODE"),16,0,32),1,120)),to_char(count("TAXABLE_AMOUNT")),to_char(su

               bstrb(dump(min("TAXABLE_AMOUNT"),16,0,32),1,120)),to_char(substrb(dum

               p(max("TAXABLE_AMOUNT"),16,0,32),1,120)),to_char(count("TAXABLE_FLAG"

               )),to_char(substrb(dump(min("TAXABLE_FLAG"),16,0,32),1,120)),to_char(

               substrb(dump(max("TAXABLE_FLAG"),16,0,32),1,120)),to_char(count("LEGA

               L_ENTITY_ID")),to_char(substrb(dump(min("LEGAL_ENTITY_ID"),16,0,32),1

               ,120)),to_char(substrb(dump(max("LEGAL_ENTITY_ID"),16,0,32),1,120)),t

               o_char(count("PARENT_LINE_ID")),to_char(substrb(dump(min("PARENT_LINE

               _ID"),16,0,32),1,120)),to_char(substrb(dump(max("PARENT_LINE_ID"),16,

               0,32),1,120)),to_char(count("DEFERRAL_EXCLUSION_FLAG")),to_char(subst

               rb(dump(min("DEFERRAL_EXCLUSION_FLAG"),16,0,32),1,120)),to_char(subst

               rb(dump(max("DEFERRAL_EXCLUSION_FLAG"),16,0,32),1,120)),to_char(count

               ("PAYMENT_TRXN_EXTENSION_ID")),to_char(substrb(dump(min("PAYMENT_TRXN

               _EXTENSION_ID"),16,0,32),1,120)),to_char(substrb(dump(max("PAYMENT_TR

               XN_EXTENSION_ID"),16,0,32),1,120)),to_char(count("RULE_END_DATE")),to

               _char(substrb(dump(min("RULE_END_DATE"),16,0,32),1,120)),to_char(subs

               trb(dump(max("RULE_END_DATE"),16,0,32),1,120)),to_char(count("PAYMENT

               _ATTRIBUTES")),to_char(substrb(dump(min("PAYMENT_ATTRIBUTES"),16,0,32

               ),1,120)),to_char(substrb(dump(max("PAYMENT_ATTRIBUTES"),16,0,32),1,1

               20)),to_char(count("APPLICATION_ID")),to_char(substrb(dump(min("APPLI

               CATION_ID"),16,0,32),1,120)),to_char(substrb(dump(max("APPLICATION_ID

               "),16,0,32),1,120)),to_char(count("BILLING_DATE")),to_char(substrb(du

               mp(min("BILLING_DATE"),16,0,32),1,120)),to_char(substrb(dump(max("BIL

               LING_DATE"),16,0,32),1,120)),to_char(count("SOURCE_DATA_KEY1")),to_ch

               ar(substrb(dump(min("SOURCE_DATA_KEY1"),16,0,32),1,120)),to_char(subs

               trb(dump(max("SOURCE_DATA_KEY1"),16,0,32),1,120)),to_char(count("SOUR

               CE_DATA_KEY2")),to_char(substrb(dump(min("SOURCE_DATA_KEY2"),16,0,32)

               ,1,120)),to_char(substrb(dump(max("SOURCE_DATA_KEY2"),16,0,32),1,120)

               ),to_char(count("SOURCE_DATA_KEY3")),to_char(substrb(dump(min("SOURCE

               _DATA_KEY3"),16,0,32),1,120)),to_char(substrb(dump(max("SOURCE_DATA_K

               EY3"),16,0,32),1,120)),to_char(count("SOURCE_DATA_KEY4")),to_char(sub

               strb(dump(min("SOURCE_DATA_KEY4"),16,0,32),1,120)),to_char(substrb(du

               mp(max("SOURCE_DATA_KEY4"),16,0,32),1,120)),to_char(count("SOURCE_DAT

               A_KEY5")),to_char(substrb(dump(min("SOURCE_DATA_KEY5"),16,0,32),1,120

               )),to_char(substrb(dump(max("SOURCE_DATA_KEY5"),16,0,32),1,120)),to_c

               har(count("INVOICED_LINE_ACCTG_LEVEL")),to_char(substrb(dump(min("INV

               OICED_LINE_ACCTG_LEVEL"),16,0,32),1,120)),to_char(substrb(dump(max("I

               NVOICED_LINE_ACCTG_LEVEL"),16,0,32),1,120)),to_char(count("ORIGINAL_G

               L_DATE")),to_char(substrb(dump(min("ORIGINAL_GL_DATE"),16,0,32),1,120

               )),to_char(substrb(dump(max("ORIGINAL_GL_DATE"),16,0,32),1,120)),to_c

               har(count("PAYMENT_SET_ID")),to_char(substrb(dump(min("PAYMENT_SET_ID

               "),16,0,32),1,120)),to_char(substrb(dump(max("PAYMENT_SET_ID"),16,0,3

               2),1,120)),to_char(count("INTERFACE_LINE_ID")),to_char(substrb(dump(m

               in("INTERFACE_LINE_ID"),16,0,32),1,120)),to_char(substrb(dump(max("IN

               TERFACE_LINE_ID"),16,0,32),1,120)),to_char(count("INTERFACE_LINE_CONT

               EXT")),to_char(substrb(dump(min("INTERFACE_LINE_CONTEXT"),16,0,32),1,

               120)),to_char(substrb(dump(max("INTERFACE_LINE_CONTEXT"),16,0,32),1,1

               20)),to_char(count("INTERFACE_LINE_ATTRIBUTE1")),to_char(substrb(dump

               (min("INTERFACE_LINE_ATTRIBUTE1"),16,0,32),1,120)),to_char(substrb(du

               mp(max("INTERFACE_LINE_ATTRIBUTE1"),16,0,32),1,120)),to_char(count("I

               NTERFACE_LINE_ATTRIBUTE2")),to_char(substrb(dump(min("INTERFACE_LINE_

               ATTRIBUTE2"),16,0,32),1,120)),to_char(substrb(dump(max("INTERFACE_LIN

               E_ATTRIBUTE2"),16,0,32),1,120)),to_char(count("INTERFACE_LINE_ATTRIBU

               TE3")),to_char(substrb(dump(min("INTERFACE_LINE_ATTRIBUTE3"),16,0,32)

               ,1,120)),to_char(substrb(dump(max("INTERFACE_LINE_ATTRIBUTE3"),16,0,3

               2),1,120)),to_char(count("INTERFACE_LINE_ATTRIBUTE4")),to_char(substr

               b(dump(min("INTERFACE_LINE_ATTRIBUTE4"),16,0,32),1,120)),to_char(subs

               trb(dump(max("INTERFACE_LINE_ATTRIBUTE4"),16,0,32),1,120)),to_char(co

               unt("INTERFACE_LINE_ATTRIBUTE5")),to_char(substrb(dump(min("INTERFACE

               _LINE_ATTRIBUTE5"),16,0,32),1,120)),to_char(substrb(dump(max("INTERFA

               CE_LINE_ATTRIBUTE5"),16,0,32),1,120)),to_char(count("INTERFACE_LINE_A

               TTRIBUTE6")),to_char(substrb(dump(min("INTERFACE_LINE_ATTRIBUTE6"),16

               ,0,32),1,120)),to_char(substrb(dump(max("INTERFACE_LINE_ATTRIBUTE6"),

               16,0,32),1,120)),to_char(count("INTERFACE_LINE_ATTRIBUTE7")),to_char(

               substrb(dump(min("INTERFACE_LINE_ATTRIBUTE7"),16,0,32),1,120)),to_cha

               r(substrb(dump(max("INTERFACE_LINE_ATTRIBUTE7"),16,0,32),1,120)),to_c

               har(count("INTERFACE_LINE_ATTRIBUTE8")),to_char(substrb(dump(min("INT

               ERFACE_LINE_ATTRIBUTE8"),16,0,32),1,120)),to_char(substrb(dump(max("I

               NTERFACE_LINE_ATTRIBUTE8"),16,0,32),1,120)),to_char(count("BATCH_SOUR

               CE_NAME")),to_char(substrb(dump(min("BATCH_SOURCE_NAME"),16,0,32),1,1

               20)),to_char(substrb(dump(max("BATCH_SOURCE_NAME"),16,0,32),1,120)),t

               o_char(count("SET_OF_BOOKS_ID")),to_char(substrb(dump(min("SET_OF_BOO

               KS_ID"),16,0,32),1,120)),to_char(substrb(dump(max("SET_OF_BOOKS_ID"),

               16,0,32),1,120)),to_char(count("LINE_TYPE")),to_char(substrb(dump(min

               ("LINE_TYPE"),16,0,32),1,120)),to_char(substrb(dump(max("LINE_TYPE"),

               16,0,32),1,120)),to_char(count("DESCRIPTION")),to_char(substrb(dump(m

               in("DESCRIPTION"),16,0,32),1,120)),to_char(substrb(dump(max("DESCRIPT

               ION"),16,0,32),1,120)),to_char(count("CURRENCY_CODE")),to_char(substr

               b(dump(min("CURRENCY_CODE"),16,0,32),1,120)),to_char(substrb(dump(max

               ("CURRENCY_CODE"),16,0,32),1,120)),to_char(count("AMOUNT")),to_char(s

               ubstrb(dump(min("AMOUNT"),16,0,32),1,120)),to_char(substrb(dump(max("

               AMOUNT"),16,0,32),1,120)),to_char(count("CUST_TRX_TYPE_NAME")),to_cha

               r(substrb(dump(min("CUST_TRX_TYPE_NAME"),16,0,32),1,120)),to_char(sub

               strb(dump(max("CUST_TRX_TYPE_NAME"),16,0,32),1,120)),to_char(count("C

               UST_TRX_TYPE_ID")),to_char(substrb(dump(min("CUST_TRX_TYPE_ID"),16,0,

               32),1,120)),to_char(substrb(dump(max("CUST_TRX_TYPE_ID"),16,0,32),1,1

               20)),to_char(count("TERM_NAME")),to_char(substrb(dump(min("TERM_NAME"

               ),16,0,32),1,120)),to_char(substrb(dump(max("TERM_NAME"),16,0,32),1,1

               20)),to_char(count("TERM_ID")),to_char(substrb(dump(min("TERM_ID"),16

               ,0,32),1,120)),to_char(substrb(dump(max("TERM_ID"),16,0,32),1,120)),t

               o_char(count("ORIG_SYSTEM_BILL_CUSTOMER_REF")),to_char(substrb(dump(m

               in("ORIG_SYSTEM_BILL_CUSTOMER_REF"),16,0,32),1,120)),to_char(substrb(

               dump(max("ORIG_SYSTEM_BILL_CUSTOMER_REF"),16,0,32),1,120)),to_char(co

               unt("ORIG_SYSTEM_BILL_CUSTOMER_ID")),to_char(substrb(dump(min("ORIG_S

               YSTEM_BILL_CUSTOMER_ID"),16,0,32),1,120)),to_char(substrb(dump(max("O

               RIG_SYSTEM_BILL_CUSTOMER_ID"),16,0,32),1,120)),to_char(count("ORIG_SY

               STEM_BILL_ADDRESS_REF")),to_char(substrb(dump(min("ORIG_SYSTEM_BILL_A

               DDRESS_REF"),16,0,32),1,120)),to_char(substrb(dump(max("ORIG_SYSTEM_B

               ILL_ADDRESS_REF"),16,0,32),1,120)),to_char(count("ORIG_SYSTEM_BILL_AD

               DRESS_ID")),to_char(substrb(dump(min("ORIG_SYSTEM_BILL_ADDRESS_ID"),1

               6,0,32),1,120)),to_char(substrb(dump(max("ORIG_SYSTEM_BILL_ADDRESS_ID

               "),16,0,32),1,120)),to_char(count("ORIG_SYSTEM_BILL_CONTACT_REF")),to

               _char(substrb(dump(min("ORIG_SYSTEM_BILL_CONTACT_REF"),16,0,32),1,120

               )),to_char(substrb(dump(max("ORIG_SYSTEM_BILL_CONTACT_REF"),16,0,32),

               1,120)),to_char(count("ORIG_SYSTEM_BILL_CONTACT_ID")),to_char(substrb

               (dump(min("ORIG_SYSTEM_BILL_CONTACT_ID"),16,0,32),1,120)),to_char(sub

               strb(dump(max("ORIG_SYSTEM_BILL_CONTACT_ID"),16,0,32),1,120)),to_char

               (count("ORIG_SYSTEM_SHIP_CUSTOMER_REF")),to_char(substrb(dump(min("OR

               IG_SYSTEM_SHIP_CUSTOMER_REF"),16,0,32),1,120)),to_char(substrb(dump(m

               ax("ORIG_SYSTEM_SHIP_CUSTOMER_REF"),16,0,32),1,120)),to_char(count("O

               RIG_SYSTEM_SHIP_CUSTOMER_ID")),to_char(substrb(dump(min("ORIG_SYSTEM_

               SHIP_CUSTOMER_ID"),16,0,32),1,120)),to_char(substrb(dump(max("ORIG_SY

               STEM_SHIP_CUSTOMER_ID"),16,0,32),1,120)),to_char(count("ORIG_SYSTEM_S

               HIP_ADDRESS_REF")),to_char(substrb(dump(min("ORIG_SYSTEM_SHIP_ADDRESS

               _REF"),16,0,32),1,120)),to_char(substrb(dump(max("ORIG_SYSTEM_SHIP_AD

               DRESS_REF"),16,0,32),1,120)),to_char(count("ORIG_SYSTEM_SHIP_ADDRESS_

               ID")),to_char(substrb(dump(min("ORIG_SYSTEM_SHIP_ADDRESS_ID"),16,0,32

               ),1,120)),to_char(substrb(dump(max("ORIG_SYSTEM_SHIP_ADDRESS_ID"),16,

               0,32),1,120)),to_char(count("ORIG_SYSTEM_SHIP_CONTACT_REF")),to_char(

               substrb(dump(min("ORIG_SYSTEM_SHIP_CONTACT_REF"),16,0,32),1,120)),to_

               char(substrb(dump(max("ORIG_SYSTEM_SHIP_CONTACT_REF"),16,0,32),1,120)

               ),to_char(count("ORIG_SYSTEM_SHIP_CONTACT_ID")),to_char(substrb(dump(

               min("ORIG_SYSTEM_SHIP_CONTACT_ID"),16,0,32),1,120)),to_char(substrb(d

               ump(max("ORIG_SYSTEM_SHIP_CONTACT_ID"),16,0,32),1,120)),to_char(count

               ("ORIG_SYSTEM_SOLD_CUSTOMER_REF")),to_char(substrb(dump(min("ORIG_SYS

               TEM_SOLD_CUSTOMER_REF"),16,0,32),1,120)),to_char(substrb(dump(max("OR

               IG_SYSTEM_SOLD_CUSTOMER_REF"),16,0,32),1,120)),to_char(count("ORIG_SY

               STEM_SOLD_CUSTOMER_ID")),to_char(substrb(dump(min("ORIG_SYSTEM_SOLD_C

               USTOMER_ID"),16,0,32),1,120)),to_char(substrb(dump(max("ORIG_SYSTEM_S

               OLD_CUSTOMER_ID"),16,0,32),1,120)),to_char(count("LINK_TO_LINE_ID")),

               to_char(substrb(dump(min("LINK_TO_LINE_ID"),16,0,32),1,120)),to_char(

               substrb(dump(max("LINK_TO_LINE_ID"),16,0,32),1,120)),to_char(count("L

               INK_TO_LINE_CONTEXT")),to_char(substrb(dump(min("LINK_TO_LINE_CONTEXT

               "),16,0,32),1,120)),to_char(substrb(dump(max("LINK_TO_LINE_CONTEXT"),

               16,0,32),1,120)),to_char(count("LINK_TO_LINE_ATTRIBUTE1")),to_char(su

               bstrb(dump(min("LINK_TO_LINE_ATTRIBUTE1"),16,0,32),1,120)),to_char(su

               bstrb(dump(max("LINK_TO_LINE_ATTRIBUTE1"),16,0,32),1,120)),to_char(co

               unt("LINK_TO_LINE_ATTRIBUTE2")),to_char(substrb(dump(min("LINK_TO_LIN

               E_ATTRIBUTE2"),16,0,32),1,120)),to_char(substrb(dump(max("LINK_TO_LIN

               E_ATTRIBUTE2"),16,0,32),1,120)),to_char(count("LINK_TO_LINE_ATTRIBUTE

               3")),to_char(substrb(dump(min("LINK_TO_LINE_ATTRIBUTE3"),16,0,32),1,1

               20)),to_char(substrb(dump(max("LINK_TO_LINE_ATTRIBUTE3"),16,0,32),1,1

               20)),to_char(count("LINK_TO_LINE_ATTRIBUTE4")),to_char(substrb(dump(m

               in("LINK_TO_LINE_ATTRIBUTE4"),16,0,32),1,120)),to_char(substrb(dump(m

               ax("LINK_TO_LINE_ATTRIBUTE4"),16,0,32),1,120)),to_char(count("LINK_TO

               _LINE_ATTRIBUTE5")),to_char(substrb(dump(min("LINK_TO_LINE_ATTRIBUTE5

               "),16,0,32),1,120)),to_char(substrb(dump(max("LINK_TO_LINE_ATTRIBUTE5

               "),16,0,32),1,120)),to_char(count("LINK_TO_LINE_ATTRIBUTE6")),to_char

               (substrb(dump(min("LINK_TO_LINE_ATTRIBUTE6"),16,0,32),1,120)),to_char

               (substrb(dump(max("LINK_TO_LINE_ATTRIBUTE6"),16,0,32),1,120)),to_char

               (count("LINK_TO_LINE_ATTRIBUTE7")),to_char(substrb(dump(min("LINK_TO_

               LINE_ATTRIBUTE7"),16,0,32),1,120)),to_char(substrb(dump(max("LINK_TO_

               LINE_ATTRIBUTE7"),16,0,32),1,120)),to_char(count("RECEIPT_METHOD_NAME

               ")),to_char(substrb(dump(min("RECEIPT_METHOD_NAME"),16,0,32),1,120)),

               to_char(substrb(dump(max("RECEIPT_METHOD_NAME"),16,0,32),1,120)),to_c

               har(count("RECEIPT_METHOD_ID")),to_char(substrb(dump(min("RECEIPT_MET

               HOD_ID"),16,0,32),1,120)),to_char(substrb(dump(max("RECEIPT_METHOD_ID

               "),16,0,32),1,120)),to_char(count("CONVERSION_TYPE")),to_char(substrb

               (dump(min("CONVERSION_TYPE"),16,0,32),1,120)),to_char(substrb(dump(ma

               x("CONVERSION_TYPE"),16,0,32),1,120)),to_char(count("CONVERSION_DATE"

               )),to_char(substrb(dump(min("CONVERSION_DATE"),16,0,32),1,120)),to_ch

               ar(substrb(dump(max("CONVERSION_DATE"),16,0,32),1,120)),to_char(count

               ("CONVERSION_RATE")),to_char(substrb(dump(min("CONVERSION_RATE"),16,0

               ,32),1,120)),to_char(substrb(dump(max("CONVERSION_RATE"),16,0,32),1,1

               20)),to_char(count("CUSTOMER_TRX_ID")),to_char(substrb(dump(min("CUST

               OMER_TRX_ID"),16,0,32),1,120)),to_char(substrb(dump(max("CUSTOMER_TRX

               _ID"),16,0,32),1,120)),to_char(count("TRX_DATE")),to_char(substrb(dum

               p(min("TRX_DATE"),16,0,32),1,120)),to_char(substrb(dump(max("TRX_DATE

               "),16,0,32),1,120)),to_char(count("GL_DATE")),to_char(substrb(dump(mi

               n("GL_DATE"),16,0,32),1,120)),to_char(substrb(dump(max("GL_DATE"),16,

               0,32),1,120)),to_char(count("TAX_INVOICE_DATE")),to_char(substrb(dump

               (min("TAX_INVOICE_DATE"),16,0,32),1,120)),to_char(substrb(dump(max("T

               AX_INVOICE_DATE"),16,0,32),1,120)),to_char(count("TAX_INVOICE_NUMBER"

               )),to_char(substrb(dump(min("TAX_INVOICE_NUMBER"),16,0,32),1,120)),to

               _char(substrb(dump(max("TAX_INVOICE_NUMBER"),16,0,32),1,120)),to_char

               (count("PAYMENT_TYPE_CODE")),to_char(substrb(dump(min("PAYMENT_TYPE_C

               ODE"),16,0,32),1,120)),to_char(substrb(dump(max("PAYMENT_TYPE_CODE"),

               16,0,32),1,120)),to_char(count("MANDATE_LAST_TRX_FLAG")),to_char(subs

               trb(dump(min("MANDATE_LAST_TRX_FLAG"),16,0,32),1,120)),to_char(substr

               b(dump(max("MANDATE_LAST_TRX_FLAG"),16,0,32),1,120)),to_char(count("D

               OCUMENT_SUB_TYPE")),to_char(substrb(dump(min("DOCUMENT_SUB_TYPE"),16,

               0,32),1,120)),to_char(substrb(dump(max("DOCUMENT_SUB_TYPE"),16,0,32),

               1,120)),to_char(count("DEFAULT_TAXATION_COUNTRY")),to_char(substrb(du

               mp(min("DEFAULT_TAXATION_COUNTRY"),16,0,32),1,120)),to_char(substrb(d

               ump(max("DEFAULT_TAXATION_COUNTRY"),16,0,32),1,120)),to_char(count("T

               RX_BUSINESS_CATEGORY")),to_char(substrb(dump(min("TRX_BUSINESS_CATEGO

               RY"),16,0,32),1,120)),to_char(substrb(dump(max("TRX_BUSINESS_CATEGORY

               "),16,0,32),1,120)),to_char(count("OVERRIDE_AUTO_ACCOUNTING_FLAG")),t

               o_char(substrb(dump(min("OVERRIDE_AUTO_ACCOUNTING_FLAG"),16,0,32),1,1

               20)),to_char(substrb(dump(max("OVERRIDE_AUTO_ACCOUNTING_FLAG"),16,0,3

               2),1,120)),to_char(count("PROMISED_COMMITMENT_AMOUNT")),to_char(subst

               rb(dump(min("PROMISED_COMMITMENT_AMOUNT"),16,0,32),1,120)),to_char(su

               bstrb(dump(max("PROMISED_COMMITMENT_AMOUNT"),16,0,32),1,120)),to_char

               (count("SOURCE_APPLICATION_ID")),to_char(substrb(dump(min("SOURCE_APP

               LICATION_ID"),16,0,32),1,120)),to_char(substrb(dump(max("SOURCE_APPLI

               CATION_ID"),16,0,32),1,120)),to_char(count("CONTRACT_LINE_ID")),to_ch

               ar(substrb(dump(min("CONTRACT_LINE_ID"),16,0,32),1,120)),to_char(subs

               trb(dump(max("CONTRACT_LINE_ID"),16,0,32),1,120)),to_char(count("CONT

               RACT_ID")),to_char(substrb(dump(min("CONTRACT_ID"),16,0,32),1,120)),t

               o_char(substrb(dump(max("CONTRACT_ID"),16,0,32),1,120)),to_char(count

               ("CONS_BILLING_NUMBER")),to_char(substrb(dump(min("CONS_BILLING_NUMBE

               R"),16,0,32),1,120)),to_char(substrb(dump(max("CONS_BILLING_NUMBER"),

               16,0,32),1,120)),to_char(count("LINE_GDF_ATTRIBUTE7")),to_char(substr

               b(dump(min("LINE_GDF_ATTRIBUTE7"),16,0,32),1,120)),to_char(substrb(du

               mp(max("LINE_GDF_ATTRIBUTE7"),16,0,32),1,120)),to_char(count("LINE_GD

               F_ATTRIBUTE8")),to_char(substrb(dump(min("LINE_GDF_ATTRIBUTE8"),16,0,

               32),1,120)),to_char(substrb(dump(max("LINE_GDF_ATTRIBUTE8"),16,0,32),

               1,120)),to_char(count("LINE_GDF_ATTRIBUTE9")),to_char(substrb(dump(mi

               n("LINE_GDF_ATTRIBUTE9"),16,0,32),1,120)),to_char(substrb(dump(max("L

               INE_GDF_ATTRIBUTE9"),16,0,32),1,120)),to_char(count("LINE_GDF_ATTRIBU

               TE10")),to_char(substrb(dump(min("LINE_GDF_ATTRIBUTE10"),16,0,32),1,1

               20)),to_char(substrb(dump(max("LINE_GDF_ATTRIBUTE10"),16,0,32),1,120)

               ),to_char(count("LINE_GDF_ATTRIBUTE11")),to_char(substrb(dump(min("LI

               NE_GDF_ATTRIBUTE11"),16,0,32),1,120)),to_char(substrb(dump(max("LINE_

               GDF_ATTRIBUTE11"),16,0,32),1,120)),to_char(count("LINE_GDF_ATTRIBUTE1

               2")),to_char(substrb(dump(min("LINE_GDF_ATTRIBUTE12"),16,0,32),1,120)

               ),to_char(substrb(dump(max("LINE_GDF_ATTRIBUTE12"),16,0,32),1,120)),t

               o_char(count("LINE_GDF_ATTRIBUTE13")),to_char(substrb(dump(min("LINE_

               GDF_ATTRIBUTE13"),16,0,32),1,120)),to_char(substrb(dump(max("LINE_GDF

               _ATTRIBUTE13"),16,0,32),1,120)),to_char(count("LINE_GDF_ATTRIBUTE14")

               ),to_char(substrb(dump(min("LINE_GDF_ATTRIBUTE14"),16,0,32),1,120)),t

               o_char(substrb(dump(max("LINE_GDF_ATTRIBUTE14"),16,0,32),1,120)),to_c

               har(count("LINE_GDF_ATTRIBUTE15")),to_char(substrb(dump(min("LINE_GDF

               _ATTRIBUTE15"),16,0,32),1,120)),to_char(substrb(dump(max("LINE_GDF_AT

               TRIBUTE15"),16,0,32),1,120)),to_char(count("LINE_GDF_ATTRIBUTE16")),t

               o_char(substrb(dump(min("LINE_GDF_ATTRIBUTE16"),16,0,32),1,120)),to_c

               har(substrb(dump(max("LINE_GDF_ATTRIBUTE16"),16,0,32),1,120)),to_char

               (count("LINE_GDF_ATTRIBUTE17")),to_char(substrb(dump(min("LINE_GDF_AT

               TRIBUTE17"),16,0,32),1,120)),to_char(substrb(dump(max("LINE_GDF_ATTRI

               BUTE17"),16,0,32),1,120)),to_char(count("LINE_GDF_ATTRIBUTE18")),to_c

               har(substrb(dump(min("LINE_GDF_ATTRIBUTE18"),16,0,32),1,120)),to_char

               (substrb(dump(max("LINE_GDF_ATTRIBUTE18"),16,0,32),1,120)),to_char(co

               unt("LINE_GDF_ATTRIBUTE19")),to_char(substrb(dump(min("LINE_GDF_ATTRI

               BUTE19"),16,0,32),1,120)),to_char(substrb(dump(max("LINE_GDF_ATTRIBUT

               E19"),16,0,32),1,120)),to_char(count("LINE_GDF_ATTRIBUTE20")),to_char

               (substrb(dump(min("LINE_GDF_ATTRIBUTE20"),16,0,32),1,120)),to_char(su

               bstrb(dump(max("LINE_GDF_ATTRIBUTE20"),16,0,32),1,120)),to_char(count

               ("RESET_TRX_DATE_FLAG")),to_char(substrb(dump(min("RESET_TRX_DATE_FLA

               G"),16,0,32),1,120)),to_char(substrb(dump(max("RESET_TRX_DATE_FLAG"),

               16,0,32),1,120)),to_char(count("PAYMENT_SERVER_ORDER_NUM")),to_char(s

               ubstrb(dump(min("PAYMENT_SERVER_ORDER_NUM"),16,0,32),1,120)),to_char(

               substrb(dump(max("PAYMENT_SERVER_ORDER_NUM"),16,0,32),1,120)),to_char

               (count("APPROVAL_CODE")),to_char(substrb(dump(min("APPROVAL_CODE"),16

               ,0,32),1,120)),to_char(substrb(dump(max("APPROVAL_CODE"),16,0,32),1,1

               20)),to_char(count("ADDRESS_VERIFICATION_CODE")),to_char(substrb(dump

               (min("ADDRESS_VERIFICATION_CODE"),16,0,32),1,120)),to_char(substrb(du

               mp(max("ADDRESS_VERIFICATION_CODE"),16,0,32),1,120)),to_char(count("W

               AREHOUSE_ID")),to_char(substrb(dump(min("WAREHOUSE_ID"),16,0,32),1,12

               0)),to_char(substrb(dump(max("WAREHOUSE_ID"),16,0,32),1,120)),to_char

               (count("TRANSLATED_DESCRIPTION")),to_char(substrb(dump(min("TRANSLATE

               D_DESCRIPTION"),16,0,32),1,120)),to_char(substrb(dump(max("TRANSLATED

               _DESCRIPTION"),16,0,32),1,120)),to_char(count("USER_DEFINED_FISC_CLAS

               S")),to_char(substrb(dump(min("USER_DEFINED_FISC_CLASS"),16,0,32),1,1

               20)),to_char(substrb(dump(max("USER_DEFINED_FISC_CLASS"),16,0,32),1,1

               20)),to_char(count("HEADER_ATTRIBUTE1")),to_char(substrb(dump(min("HE

               ADER_ATTRIBUTE1"),16,0,32),1,120)),to_char(substrb(dump(max("HEADER_A

               TTRIBUTE1"),16,0,32),1,120)),to_char(count("HEADER_ATTRIBUTE2")),to_c

               har(substrb(dump(min("HEADER_ATTRIBUTE2"),16,0,32),1,120)),to_char(su

               bstrb(dump(max("HEADER_ATTRIBUTE2"),16,0,32),1,120)),to_char(count("H

               EADER_ATTRIBUTE3")),to_char(substrb(dump(min("HEADER_ATTRIBUTE3"),16,

               0,32),1,120)),to_char(substrb(dump(max("HEADER_ATTRIBUTE3"),16,0,32),

               1,120)),to_char(count("HEADER_ATTRIBUTE4")),to_char(substrb(dump(min(

               "HEADER_ATTRIBUTE4"),16,0,32),1,120)),to_char(substrb(dump(max("HEADE

               R_ATTRIBUTE4"),16,0,32),1,120)),to_char(count("HEADER_ATTRIBUTE5")),t

               o_char(substrb(dump(min("HEADER_ATTRIBUTE5"),16,0,32),1,120)),to_char

               (substrb(dump(max("HEADER_ATTRIBUTE5"),16,0,32),1,120)),to_char(count

               ("HEADER_ATTRIBUTE6")),to_char(substrb(dump(min("HEADER_ATTRIBUTE6"),

               16,0,32),1,120)),to_char(substrb(dump(max("HEADER_ATTRIBUTE6"),16,0,3

               2),1,120)),to_char(count("HEADER_ATTRIBUTE7")),to_char(substrb(dump(m

               in("HEADER_ATTRIBUTE7"),16,0,32),1,120)),to_char(substrb(dump(max("HE

               ADER_ATTRIBUTE7"),16,0,32),1,120)),to_char(count("HEADER_ATTRIBUTE8")

               ),to_char(substrb(dump(min("HEADER_ATTRIBUTE8"),16,0,32),1,120)),to_c

               har(substrb(dump(max("HEADER_ATTRIBUTE8"),16,0,32),1,120)),to_char(co

               unt("HEADER_ATTRIBUTE9")),to_char(substrb(dump(min("HEADER_ATTRIBUTE9

               "),16,0,32),1,120)),to_char(substrb(dump(max("HEADER_ATTRIBUTE9"),16,

               0,32),1,120)),to_char(count("HEADER_ATTRIBUTE10")),to_char(substrb(du

               mp(min("HEADER_ATTRIBUTE10"),16,0,32),1,120)),to_char(substrb(dump(ma

               x("HEADER_ATTRIBUTE10"),16,0,32),1,120)),to_char(count("HEADER_ATTRIB

               UTE11")),to_char(substrb(dump(min("HEADER_ATTRIBUTE11"),16,0,32),1,12

               0)),to_char(substrb(dump(max("HEADER_ATTRIBUTE11"),16,0,32),1,120)),t

               o_char(count("HEADER_ATTRIBUTE12")),to_char(substrb(dump(min("HEADER_

               ATTRIBUTE12"),16,0,32),1,120)),to_char(substrb(dump(max("HEADER_ATTRI

               BUTE12"),16,0,32),1,120)),to_char(count("HEADER_ATTRIBUTE13")),to_cha

               r(substrb(dump(min("HEADER_ATTRIBUTE13"),16,0,32),1,120)),to_char(sub

               strb(dump(max("HEADER_ATTRIBUTE13"),16,0,32),1,120)),to_char(count("H

               EADER_ATTRIBUTE14")),to_char(substrb(dump(min("HEADER_ATTRIBUTE14"),1

               6,0,32),1,120)),to_char(substrb(dump(max("HEADER_ATTRIBUTE14"),16,0,3

               2),1,120)),to_char(count("HEADER_ATTRIBUTE15")),to_char(substrb(dump(

               min("HEADER_ATTRIBUTE15"),16,0,32),1,120)),to_char(substrb(dump(max("

               HEADER_ATTRIBUTE15"),16,0,32),1,120)),to_char(count("COMMENTS")),to_c

               har(substrb(dump(min("COMMENTS"),16,0,32),1,120)),to_char(substrb(dum

               p(max("COMMENTS"),16,0,32),1,120)),to_char(count("INTERNAL_NOTES")),t

               o_char(substrb(dump(min("INTERNAL_NOTES"),16,0,32),1,120)),to_char(su

               bstrb(dump(max("INTERNAL_NOTES"),16,0,32),1,120)),to_char(count("INIT

               IAL_CUSTOMER_TRX_ID")),to_char(substrb(dump(min("INITIAL_CUSTOMER_TRX

               _ID"),16,0,32),1,120)),to_char(substrb(dump(max("INITIAL_CUSTOMER_TRX

               _ID"),16,0,32),1,120)),to_char(count("USSGL_TRANSACTION_CODE_CONTEXT"

               )),to_char(substrb(dump(min("USSGL_TRANSACTION_CODE_CONTEXT"),16,0,32

               ),1,120)),to_char(substrb(dump(max("USSGL_TRANSACTION_CODE_CONTEXT"),

               16,0,32),1,120)),to_char(count("USSGL_TRANSACTION_CODE")),to_char(sub

               strb(dump(min("USSGL_TRANSACTION_CODE"),16,0,32),1,120)),to_char(subs

               trb(dump(max("USSGL_TRANSACTION_CODE"),16,0,32),1,120)),to_char(count

               ("ACCTD_AMOUNT")),to_char(substrb(dump(min("ACCTD_AMOUNT"),16,0,32),1

               ,120)),to_char(substrb(dump(max("ACCTD_AMOUNT"),16,0,32),1,120)),to_c

               har(count("CUSTOMER_BANK_ACCOUNT_ID")),to_char(substrb(dump(min("CUST

               OMER_BANK_ACCOUNT_ID"),16,0,32),1,120)),to_char(substrb(dump(max("CUS

               TOMER_BANK_ACCOUNT_ID"),16,0,32),1,120)),to_char(count("CUSTOMER_BANK

               _ACCOUNT_NAME")),to_char(substrb(dump(min("CUSTOMER_BANK_ACCOUNT_NAME

               "),16,0,32),1,120)),to_char(substrb(dump(max("CUSTOMER_BANK_ACCOUNT_N

               AME"),16,0,32),1,120)),to_char(count("UOM_CODE")),to_char(substrb(dum

               p(min("UOM_CODE"),16,0,32),1,120)),to_char(substrb(dump(max("UOM_CODE

               "),16,0,32),1,120)),to_char(count("UOM_NAME")),to_char(substrb(dump(m

               in("UOM_NAME"),16,0,32),1,120)),to_char(substrb(dump(max("UOM_NAME"),

               16,0,32),1,120)),to_char(count("DOCUMENT_NUMBER_SEQUENCE_ID")),to_cha

               r(substrb(dump(min("DOCUMENT_NUMBER_SEQUENCE_ID"),16,0,32),1,120)),to

               _char(substrb(dump(max("DOCUMENT_NUMBER_SEQUENCE_ID"),16,0,32),1,120)

               ) from "AR"."RA_INTERFACE_LINES_ALL" t  /*

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NI

               L,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV

               ,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NI

               L,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV

               ,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NI

               L,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV

               ,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NI

               L,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV

               ,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NI

               L,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV

               ,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NI

               L,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV

               ,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NI

               L,NIL,NDV,NIL,NIL,NDV,NIL,NIL,ACL,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV

               ,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NI

               L,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV

               ,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NI

               L,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV

               ,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NI

               L,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV

               ,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NI

               L,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV

               ,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,

               NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,N

               IL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/

         Rationale

            The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

            This part of database time may be improved by the SQL Tuning Advisor.

         Rationale

            Database time for this SQL was divided as follows: 100% for SQL

            execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

            execution.

         Rationale

            SQL statement with SQL_ID "f6wqn8h38mktq" was executed 1 times and had

            an average elapsed time of 627 seconds.

         Rationale

            At least one execution of the statement ran in parallel.

       

       

      Finding 2: Top Segments by "User I/O" and "Cluster"

      Impact is 2.64 active sessions, 53.06% of total activity.

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

      Individual database segments responsible for significant "User I/O" and

      "Cluster" waits were found.

       

         Recommendation 1: Segment Tuning

         Estimated benefit is .99 active sessions, 19.78% of total activity.

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

         Action

            Run "Segment Advisor" on TABLE "AR.RA_CUSTOMER_TRX_LINES_ALL" with

            object ID 29149.

            Related Object

               Database object with ID 29149.

         Action

            Investigate application logic involving I/O on TABLE

            "AR.RA_CUSTOMER_TRX_LINES_ALL" with object ID 29149.

            Related Object

               Database object with ID 29149.

         Action

            Look at the "Top SQL Statements" finding for SQL statements consuming

            significant I/O on this segment. For example, the UPDATE statement with

            SQL_ID "85ag5gkm9wzmb" is responsible for 99% of "User I/O" and

            "Cluster" waits for this segment.

         Rationale

            The I/O usage statistics for the object are: 64 full object scans,

            70087134 physical reads, 52860 physical writes and 0 direct reads.

       

         Recommendation 2: Segment Tuning

         Estimated benefit is .98 active sessions, 19.59% of total activity.

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

         Action

            Run "Segment Advisor" on TABLE "AR.RA_CUST_TRX_LINE_GL_DIST_ALL" with

            object ID 29185.

            Related Object

               Database object with ID 29185.

         Action

            Investigate application logic involving I/O on TABLE

            "AR.RA_CUST_TRX_LINE_GL_DIST_ALL" with object ID 29185.

            Related Object

               Database object with ID 29185.

         Action

            Look at the "Top SQL Statements" finding for SQL statements consuming

            significant I/O on this segment. For example, the UPDATE statement with

            SQL_ID "85ag5gkm9wzmb" is responsible for 100% of "User I/O" and

            "Cluster" waits for this segment.

         Rationale

            The I/O usage statistics for the object are: 74 full object scans,

            65057017 physical reads, 42162 physical writes and 0 direct reads.

       

         Recommendation 3: Segment Tuning

         Estimated benefit is .3 active sessions, 6.01% of total activity.

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

         Action

            Run "Segment Advisor" on TABLE "AR.RA_CUSTOMER_TRX_ALL" with object ID

            29054.

            Related Object

               Database object with ID 29054.

         Action

            Investigate application logic involving I/O on TABLE

            "AR.RA_CUSTOMER_TRX_ALL" with object ID 29054.

            Related Object

               Database object with ID 29054.

         Action

            Look at the "Top SQL Statements" finding for SQL statements consuming

            significant I/O on this segment. For example, the UPDATE statement with

            SQL_ID "85ag5gkm9wzmb" is responsible for 53% of "User I/O" and

            "Cluster" waits for this segment.

         Rationale

            The I/O usage statistics for the object are: 245 full object scans,

            7438984 physical reads, 114806 physical writes and 0 direct reads.

       

         Recommendation 4: Segment Tuning

         Estimated benefit is .23 active sessions, 4.57% of total activity.

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

         Action

            Investigate application logic involving I/O on TABLE

            "AR.RA_INTERFACE_LINES_ALL" with object ID 28266.

            Related Object

               Database object with ID 28266.

         Action

            Look at the "Top SQL Statements" finding for SQL statements consuming

            significant I/O on this segment. For example, the SELECT statement with

            SQL_ID "f6wqn8h38mktq" is responsible for 48% of "User I/O" and

            "Cluster" waits for this segment.

         Rationale

            The I/O usage statistics for the object are: 3544 full object scans,

            3165892 physical reads, 480220 physical writes and 2100125 direct reads.

       

         Recommendation 5: Segment Tuning

         Estimated benefit is .16 active sessions, 3.11% of total activity.

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

         Action

            Run "Segment Advisor" on TABLE "GL.GL_JE_HEADERS" with object ID 35192.

            Related Object

               Database object with ID 35192.

         Action

            Investigate application logic involving I/O on TABLE "GL.GL_JE_HEADERS"

            with object ID 35192.

            Related Object

               Database object with ID 35192.

         Action

            Look at the "Top SQL Statements" finding for SQL statements consuming

            significant I/O on this segment. For example, the SELECT statement with

            SQL_ID "gy02p15c6bf0w" is responsible for 70% of "User I/O" and

            "Cluster" waits for this segment.

         Rationale

            The I/O usage statistics for the object are: 65 full object scans,

            61819531 physical reads, 37636 physical writes and 0 direct reads.

       

         Symptoms That Led to the Finding:

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

            Wait class "User I/O" was consuming significant database time.

            Impact is 3.56 active sessions, 71.36% of total activity.

       

       

      Finding 3: Undersized SGA

      Impact is .94 active sessions, 18.91% of total activity.

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

      The SGA was inadequately sized, causing additional I/O or hard parses.

      The value of parameter "sga_target" was "24576 M" during the analysis period.

       

         Recommendation 1: Database Configuration

         Estimated benefit is .94 active sessions, 18.91% of total activity.

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

         Action

            Increase the size of the SGA by setting the parameter "sga_target" to

            46080 M.

       

         Symptoms That Led to the Finding:

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

            Wait class "User I/O" was consuming significant database time.

            Impact is 3.56 active sessions, 71.36% of total activity.

       

       

      Finding 4: Undersized PGA

      Impact is .28 active sessions, 5.66% of total activity.

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

      The PGA was inadequately sized, causing additional I/O to temporary

      tablespaces to consume significant database time.

      The value of parameter "pga_aggregate_target" was "6144 M" during the analysis

      period.

       

         No recommendations are available.

       

         Symptoms That Led to the Finding:

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

            Wait class "User I/O" was consuming significant database time.

            Impact is 3.56 active sessions, 71.36% of total activity.

       

       

      Finding 5: PL/SQL Execution

      Impact is .19 active sessions, 3.9% of total activity.

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

      PL/SQL execution consumed significant database time.

       

         Recommendation 1: SQL Tuning

         Estimated benefit is .13 active sessions, 2.62% of total activity.

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

         Action

            Tune the entry point PL/SQL "SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC"

            of type "PACKAGE" and ID 2301. Refer to the PL/SQL documentation for

            addition information.

         Rationale

            11696 seconds spent in executing PL/SQL

            "SYS.DBMS_SPACE.OBJECT_SPACE_USAGE" of type "PACKAGE" and ID 2301.

       

         Recommendation 2: SQL Tuning

         Estimated benefit is .05 active sessions, .95% of total activity.

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

         Action

            Tune the entry point PL/SQL "APPS.XXSD_AR_RECEIPTS.XXSD_AR_RECEIPTS" of

            type "PACKAGE" and ID 519971. Refer to the PL/SQL documentation for

            addition information.

         Rationale

            584 seconds spent in executing PL/SQL

            "APPS.FND_FLEX_SERVER1.VALIDATE_STRUCT" of type "PACKAGE" and ID 52299.

         Rationale

            271 seconds spent in executing PL/SQL

            "APPS.ARP_XLA_EVENTS.CREATE_EVENTS" of type "PACKAGE" and ID 414875.

         Rationale

            156 seconds spent in executing PL/SQL

            "APPS.FND_PLSQL_CACHE.GENERIC_1TOM_GET_VALUES" of type "PACKAGE" and ID

            246636.

         Rationale

            135 seconds spent in executing PL/SQL

            "APPS.FND_FLEX_VAL_UTIL.VALIDATE_VALUE_SSV" of type "PACKAGE" and ID

            52432.

         Rationale

            114 seconds spent in executing PL/SQL "SYS.DBMS_SQL.BIND_VARIABLE#2" of

            type "PACKAGE" and ID 2318.

       

         Recommendation 3: SQL Tuning

         Estimated benefit is .01 active sessions, .16% of total activity.

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

         Action

            Tune the entry point PL/SQL

            "APPS.XXSD_AR_TXN_UPLOAD_PKG.ARCM_APPLICATION" of type "PACKAGE" and ID

            520089. Refer to the PL/SQL documentation for addition information.

         Rationale

            52 seconds spent in executing PL/SQL "APPS.ARP_STANDARD.DEBUG" of type

            "PACKAGE" and ID 47548.

         Rationale

            52 seconds spent in executing PL/SQL

            "APPS.ARP_ALLOCATION_PKG.ALLOCATE_TAX" of type "PACKAGE" and ID 47565.

         Rationale

            52 seconds spent in executing PL/SQL

            "APPS.ARP_DET_DIST_PKG.TRX_LEVEL_DIRECT_CASH_APPLY" of type "PACKAGE"

            and ID 414842.

       

         Recommendation 4: SQL Tuning

         Estimated benefit is .01 active sessions, .11% of total activity.

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

         Action

            Tune the entry point PL/SQL

            "APPS.XLA_ACCOUNTING_PKG.UNIT_PROCESSOR_BATCH" of type "PACKAGE" and ID

            421073. Refer to the PL/SQL documentation for addition information.

         Rationale

            198 seconds spent in executing PL/SQL

            "APPS.XLA_00222_AAD_S_000002_PKG.CREATEJOURNALENTRIES" of type "PACKAGE"

            and ID 435516.

         Rationale

            62 seconds spent in executing PL/SQL

            "APPS.XLA_AE_LINES_PKG.SETLINEACCTATTRS" of type "PACKAGE" and ID

            421139.

       

         Recommendation 5: SQL Tuning

         Estimated benefit is 0 active sessions, .07% of total activity.

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

         Action

            Tune the entry point PL/SQL "APPS.XXSD_PAYMENT_DETAIL_REPORT" of type

            "PROCEDURE" and ID 592432. Refer to the PL/SQL documentation for

            addition information.

         Rationale

            Subprogam ID 31 of PL/SQL ID 2208 spent 73 seconds in execution.

         Rationale

            62 seconds spent in executing PL/SQL "APPS.XXSD_PAYMENT_DETAIL_REPORT"

            of type "PROCEDURE" and ID 592432.

         Rationale

            52 seconds spent in executing PL/SQL "SYS.UTL_FILE.PUT_LINE" of type

            "PACKAGE" and ID 2208.

         Rationale

            Subprogam ID 29 of PL/SQL ID 2208 spent 52 seconds in execution.

       

       

       

      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

       

                Additional Information

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

       

      Miscellaneous Information

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

      Wait class "Application" was not consuming significant database time.

      Wait class "Commit" was not consuming significant database time.

      Wait class "Concurrency" was not consuming significant database time.

      Wait class "Configuration" was not consuming significant database time.

      CPU was not a bottleneck for the instance.

      Wait class "Network" was not consuming significant database time.

      Session connect and disconnect calls were not consuming significant database

      time.

      Hard parsing of SQL statements was not consuming significant database time.

       

      The database's maintenance windows were active during 20% of the analysis

      period.

        • 1. Re: Need understanding on the ADDM
          Asif Muhammad

          Hi Karan,

           

          ADDM report requires Sql tuning advisor and Segment advisor to run apart from the Memory allocation concern you mentioned of. Therefore please try this out on a replica instance (TEST) and then eventually move out to the Prod.

           

          What is your RAM capacity on the Server, please provide Server configuration details.

           

          In addition to that, please confirm when was the last time you ran Gather Schema Statistics.

           

          Thanks &

          Best Regards,

          • 2. Re: Need understanding on the ADDM
            Karan Kukreja

            Hi Asif ,

             

            Thanks for writing.

             

            The update statement which is causing the trouble is a seeded oracle program so we have checking that with oracle. ( Auto_invoice_master).

            GSS was scheduled to run at 20% till last week ( runs every week) while i just made it to run for auto_sample_size by giving the percent as 0. I have checked and found that most of the tables had good stats , though one of the programs has started to take too much time after that. So for now i have reverted it back to 20%.

            Is 20% good or should i go for more ?

             

            DB node RAM on prod is 48GB .

             

            I am not very sure of how to run sql tuning advisor and sql segment advisor. I have never run that. Let me check on the steps.

             

            Regards

            Karan

            • 3. Re: Need understanding on the ADDM
              Asif Muhammad

              Hi Karan,

               

              If your DB node has 48 GB and there are no other applications/program running on the server than I believe you still have for room for SGA/PGA resize.

               

              GSS was scheduled to run at 20% till last week ( runs every week) while i just made it to run for auto_sample_size by giving the percent as 0. I have checked and found that most of the tables had good stats , though one of the programs has started to take too much time after that. So for now i have reverted it back to 20%.

              10% - 20% is the percentage which I usually go with and this works well for me. Anyways that being said 10% default would do the trick in most cases.

               

              The update statement which is causing the trouble is a seeded oracle program so we have checking that with oracle. ( Auto_invoice_master).

              Yes as recommended, Please get confirmation from SR as well.

               

              Please also see note:

              How To Setup AutoInvoice For Optimal Performance (Doc ID 1083467.1)

              Troubleshooting AutoInvoice Performance Issues (Doc ID 1089196.1)

               

              Thanks &

              Best Regards

              • 4. Re: Need understanding on the ADDM
                Karan Kukreja

                Hi Asif,

                 

                I wonder how just 10/20% be fine ? I am just saying this because since the last 3-4 years , the systems on which ive been working all have 80 percent set

                 

                And then i just moved to another organisation. And here it was 20%. I studied and found that people had started giving auto_sample_size from 11G onwards. So i set it to auto sample size. I got a bad performance mail though ive been checking all the stats and they were good and in place. reset it back to 20%.

                 

                 

                And thank you for the Metalink ID's brother. Ive already gone through them and the patches suggested are in place in the dev. performance on the dev is coming out good and by next week we may conclude to move the patches to prod. ''thanks.

                • 5. Re: Need understanding on the ADDM
                  Srini Chavali-Oracle

                  This seems to be a duplicate of Auto Invoice Program taking too much time : problem with update sql

                   

                  10-20% for stats gathering is sufficient for most purposes - see MOS Doc 1586374.1 for details

                  • 6. Re: Need understanding on the ADDM
                    Karan Kukreja

                    Hi Srini,

                     

                    This is not exactly the duplicate. That thread  i had created was exclusively  for the sql part and as you can see I was actively discussing that. . Whereas this is for understanding more on the ADDM part. And somehow got drifted towards the same troubling SQL .

                     

                    Anyway I am glad to know that setting it back to 20% is not a bad option after reading response from you and Asif.

                     

                    If this condition violates any rules of the forums , please feel free to instruct me and do the needful Sir. Thank You.

                     

                     

                    Regards

                    Karan

                    • 7. Re: Need understanding on the ADDM
                      Hussein Sawwan-Oracle

                      Karan,

                       

                      The best approach would be running it twice (or more), once with 10% then 20% and see how your system behaves. We used to use 10% for all schemas and 40% for HRMS schemas (based on many runs) and we were happy with the performance.

                       

                      Thanks,

                      Hussein

                      • 8. Re: Need understanding on the ADDM
                        Karan Kukreja

                        Hi Hussein ,

                         

                        I have set it to 20% for this weekend.  We do not have HRMS , we have financials implemented and that too mainly using Auto invoice , credit refund and some reports.

                         

                        I am just wondering why did auto_sample_size ( by setting it to 0 under GSS from front end) did not help me much.  Anyway thats fine .. i am happy to hear from all the experts on this. Thank You very much.

                         

                        regards

                        Karan

                        • 9. Re: Need understanding on the ADDM
                          Hussein Sawwan-Oracle

                          Hi Karan,

                           

                           

                           

                          Do you have Patch 13443215 applied? -- Use Of AUTO_SAMPLE_SIZE With Apps 12.1 (Doc ID 1435734.1)

                           

                           

                           

                          Thanks,

                          Hussein

                          • 10. Re: Need understanding on the ADDM
                            Karan Kukreja

                            Hi Hussein,

                             

                            Yes i have that patch in place :

                             

                            SQL> select bug_number from apps.ad_bugs where bug_number='13443215';

                             

                             

                            BUG_NUMBER

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

                            13443215

                             

                             

                             

                            But it seems it did not help and i was hearing peformance issues. Hence i had set it back to run at 20%.

                             

                            Regards

                            Karan

                            • 11. Re: Need understanding on the ADDM
                              Asif Muhammad

                              Hi Karan,

                               

                              The respective patch ensures the Auto Sample Size is used, and the value should be of 0.

                               

                              Please refer:

                              Use Of AUTO_SAMPLE_SIZE With Apps 12.1 (Doc ID 1435734.1)

                               

                              Anyhow , How is the performance now after you ran the GSS during the weekend?

                               

                              Thanks &

                              Best Regards,

                              • 12. Re: Need understanding on the ADDM
                                Karan Kukreja

                                Exactly Asif , that' s why i gave it to 0.

                                 

                                Well , i have not heard any complaint so far. Could be 2 reason :

                                 

                                a) Either its working OK or

                                b) they are not yet stuck

                                 

                                Let me wait till the EOD. or may be till tomorrow.

                                 

                                Regards

                                Karan

                                • 13. Re: Need understanding on the ADDM
                                  Asif Muhammad

                                  Hi Karan,

                                   

                                  Well , i have not heard any complaint so far.

                                  Great! Let us know of the status and thanks for the update.

                                   

                                  Best Regards,

                                  Asif

                                  • 14. Re: Need understanding on the ADDM
                                    Karan Kukreja

                                    Sure.. I will.. Thank you

                                    1 2 Previous Next