AR Post Adoption Adjustments Transactions

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Post Adoption Adjustments Transactions Report (XML)
Short Name: ARXPADJT_XML
DB package: AR_ARXPADJT_XML
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
Parameter Name SQL text Validation
Period To Date
 
Period Start Date
 
Ledger Name
 
Transaction Number To
 
LOV Oracle
Transaction Number From
 
LOV Oracle
Period To
 
LOV Oracle
Period From
 
LOV Oracle
Ledger
 
LOV Oracle
Ask a question