[Oracel]R12標準報表AR: Journal Entries Report拆解

 20220301 Patch 32920876: JOURNAL ENTRIES REPORT PERFORMANCE AR-ARRGTA: -1555 ORA-01555: SNAPSHOT TOO OLD，已正常

SELECT ADJ.SET_OF_BOOKS_ID,
       ADJ.ORG_ID,
       ADJ.GL_DATE,
       CT.INVOICE_CURRENCY_CODE,
       ARD.CODE_COMBINATION_ID,
       NVL(ARD.AMOUNT_DR, 0) ENTERED_DR,
       NVL(ARD.AMOUNT_CR, 0) ENTERED_CR,
       NVL(ARD.ACCTD_AMOUNT_DR, 0) ACCTD_DR,
       NVL(ARD.ACCTD_AMOUNT_CR, 0) ACCTD_CR,
       ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ARRGTA_FUNCTION_MAPPING',
                                             'ADJ_' || ARD.SOURCE_TYPE) SOURCE_TYPE,
       GCC.SEGMENT1 ||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6 ACCOUNT_ID
  FROM RA_CUSTOMER_TRX_ALL   CT,
       RA_CUST_TRX_TYPES_ALL CTT,
       AR_DISTRIBUTIONS_ALL  ARD,
       HZ_CUST_ACCOUNTS_ALL  CUST,
       AR_ADJUSTMENTS_ALL    ADJ,
       GL_CODE_COMBINATIONS  GCC
 WHERE 1 = 1
   AND ARD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND NVL(ADJ.POSTABLE, 'Y') = 'Y'
   AND ADJ.ADJUSTMENT_ID = ARD.SOURCE_ID
   AND ARD.SOURCE_TABLE = 'ADJ'
   AND ADJ.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
   AND CTT.CUST_TRX_TYPE_ID = CT.CUST_TRX_TYPE_ID
   AND CTT.ORG_ID = CT.ORG_ID
   AND ARD.ORG_ID = CT.ORG_ID
   AND ADJ.ORG_ID = CT.ORG_ID
   AND CUST.CUST_ACCOUNT_ID =
       DECODE(CTT.TYPE, 'BR', CT.DRAWEE_ID, CT.BILL_TO_CUSTOMER_ID)
   AND CT.INVOICE_CURRENCY_CODE = CT.INVOICE_CURRENCY_CODE
   AND ADJ.GL_DATE BETWEEN TO_DATE('2021/12/01', 'YYYY/MM/DD') AND
       TO_DATE('2021/12/31', 'YYYY/MM/DD')
   AND CT.ORG_ID = 136
UNION ALL
SELECT CT.SET_OF_BOOKS_ID SOB_ID,
       CTLGD.ORG_ID,
       CTLGD.GL_DATE GL_DATE,
       CT.INVOICE_CURRENCY_CODE CURRENCY,
       CTLGD.CODE_COMBINATION_ID CCID,
       TO_NUMBER(
       DECODE(CTLGD.ACCOUNT_CLASS,'REC',
              DECODE(SIGN(NVL(CTLGD.AMOUNT, 0)),-1,NULL,NVL(CTLGD.AMOUNT, 0)) ,
              DECODE(SIGN(NVL(CTLGD.AMOUNT, 0)),-1,-NVL(CTLGD.AMOUNT, 0),NULL)),
              99999999) ENTERED_DR,
       TO_NUMBER(
       DECODE(CTLGD.ACCOUNT_CLASS,'REC',
              DECODE(SIGN(NVL(CTLGD.AMOUNT, 0)),-1,-NVL(CTLGD.AMOUNT, 0),NULL),
              DECODE(SIGN(NVL(CTLGD.AMOUNT, 0)),-1,NULL,NVL(CTLGD.AMOUNT, 0))),
              99999999) ENTERED_CR,
       TO_NUMBER(
       DECODE(CTLGD.ACCOUNT_CLASS,
              'REC',
              DECODE(SIGN(NVL(CTLGD.ACCTD_AMOUNT, 0)),-1,NULL,NVL(CTLGD.ACCTD_AMOUNT, 0)),
              DECODE(SIGN(NVL(CTLGD.ACCTD_AMOUNT, 0)),-1,-NVL(CTLGD.ACCTD_AMOUNT, 0),NULL)),
              99999999) ACCTD_DR,
       TO_NUMBER(
       DECODE(CTLGD.ACCOUNT_CLASS,
              'REC',
              DECODE(SIGN(NVL(CTLGD.ACCTD_AMOUNT, 0)),-1,-NVL(CTLGD.ACCTD_AMOUNT, 0),NULL),
              DECODE(SIGN(NVL(CTLGD.ACCTD_AMOUNT, 0)),-1,NULL,NVL(CTLGD.ACCTD_AMOUNT, 0))),
              99999999) ACCTD_CR,
       ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ARRGTA_FUNCTION_MAPPING',
                                             DECODE(CTT.TYPE,
                                                    'CM',
                                                    'CM_',
                                                    'DM',
                                                    'DM_',
                                                    'CB',
                                                    'CB_',
                                                    'INV_') ||
                                             NVL(CTLGD.ACCOUNT_CLASS, 'REV')) SOURCE_TYPE,
       GCC.SEGMENT1 ||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6 ACCOUNT_ID
  FROM RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD,
       RA_CUSTOMER_TRX_ALL          CT,
       RA_CUST_TRX_TYPES_ALL        CTT,
       HZ_CUST_ACCOUNTS_ALL         CUST,
       GL_CODE_COMBINATIONS         GCC
 WHERE 1=1
   AND CTLGD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID 
   AND CTLGD.EVENT_ID IS NOT NULL
   AND CTLGD.ACCOUNT_SET_FLAG = 'N'
   AND CTLGD.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
   AND CT.COMPLETE_FLAG = 'Y'
   AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
   AND CT.ORG_ID = CTT.ORG_ID
   AND CT.ORG_ID = CTLGD.ORG_ID
   AND CUST.CUST_ACCOUNT_ID = CT.BILL_TO_CUSTOMER_ID
   AND DECODE(CTLGD.ACCOUNT_CLASS,
              'ROUND',
              (NVL(CTLGD.AMOUNT, 0) + NVL(CTLGD.ACCTD_AMOUNT, 0)),
              1) != DECODE(CTLGD.ACCOUNT_CLASS, 'ROUND', 0, 2)
   AND CTT.TYPE IN ('INV', 'GUAR', 'DEP', 'CM', 'DM', 'CB')
   AND CTLGD.GL_DATE BETWEEN TO_DATE('2021/12/01', 'YYYY/MM/DD') AND
       TO_DATE('2021/12/31', 'YYYY/MM/DD')
   AND CTLGD.ORG_ID = 136