AR Post Adoption Adjustments Transactions

SELECT TRX.TRX_NUMBER AS TRANSACTION_NUMBER ,
  MIN (TRX.TRX_DATE) TRANSACTION_DATE,
  MIN (ev.event_date) GL_DATE,
  MIN(PARTY.PARTY_NAME) CUSTOMER_NAME,
  MIN(HZCUST.ACCOUNT_NUMBER) ACCOUNT_NUMBER,
  CLCK.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS,
  MIN( (ARSYS.VRM_ADOPTION_PERIOD)) ADOPTION_PERIOD,
  MIN( (ARL.MEANING)) TRX_CLASS,
  SUM( DECODE(XLA.ACCOUNTING_CLASS_CODE,'REVENUE',NVL(XLA.ACCOUNTED_DR,0),0)) ACCT_REV_DEBIT_AMOUNT,
  SUM( DECODE(XLA.ACCOUNTING_CLASS_CODE,'REVENUE',NVL(XLA.ACCOUNTED_CR,0),0)) ACCT_REV_CREDIT_AMOUNT,
  SUM(DECODE(XLA.ACCOUNTING_CLASS_CODE,'UNEARN',NVL(XLA.ACCOUNTED_DR,0),0)) ACCT_UNEARN_DEBIT_AMOUNT,
  SUM( DECODE(XLA.ACCOUNTING_CLASS_CODE,'UNEARN',NVL(XLA.ACCOUNTED_CR,0),0)) ACCT_UNEARN_CREDIT_AMOUNT
FROM RA_CUSTOMER_TRX TRX,
  HZ_CUST_ACCOUNTS HZCUST,
  HZ_PARTIES PARTY,
  XLA_TRANSACTION_ENTITIES_upg XTE,
  XLA_EVENTS ev,
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XLA,
  AR_SYSTEM_PARAMETERS_ALL ARSYS,
  GL_CODE_COMBINATIONS_KFV CLCK,
  RA_CUST_TRX_TYPES TRXTYPE,
  AR_LOOKUPS ARL
WHERE TRX.CUSTOMER_TRX_ID      =XTE.SOURCE_ID_INT_1
AND XTE.application_id         =222
AND XTE.ENTITY_CODE            = 'TRANSACTIONS'
AND TRX.BILL_TO_CUSTOMER_ID    =HZCUST.CUST_ACCOUNT_ID
AND HZCUST.PARTY_ID            =PARTY.PARTY_ID
AND XTE.ENTITY_ID              =XAH.ENTITY_ID
AND XTE.entity_id              = ev.entity_id
AND ev.event_id                = xah.event_id
AND XAH.AE_HEADER_ID           =XLA.AE_HEADER_ID
AND xla.CODE_COMBINATION_ID    =CLCK.CODE_COMBINATION_ID
AND TRX.REV_REC_APPLICATION   IN ('VRM_BILLING','VRM_SOURCE')
AND TRX.DOCUMENT_TYPE_ID IS NOT NULL
AND ARSYS.VRM_ADOPTION_PERIOD IS NOT NULL
AND ev.event_DATE             >=ARSYS.VRM_ADOPT_PERIOD_START_DATE
AND XLA.ACCOUNTING_CLASS_CODE IN('REVENUE','UNEARN' )
AND TRX.CUST_TRX_TYPE_ID       =TRXTYPE.CUST_TRX_TYPE_ID
AND TRXTYPE.TYPE               =ARL.LOOKUP_CODE
AND ARL.LOOKUP_TYPE            ='INV/CM'
AND TRXTYPE.type              IN ('INV','CM')
&FINAL_WHERE_CLAUSE
GROUP BY CLCK.CONCATENATED_SEGMENTS,
  TRX.TRX_NUMBER
ORDER BY TRX.TRX_NUMBER