QUERY FOR AR - XLA - GL RELATION IN EBS R12 FOR ADJUSTMENTS GL CATEGORY

  SELECT GL_JE_LINES.JE_HEADER_ID,
  GL_JE_LINES.JE_LINE_NUM,
  AR_ADJUSTMENTS_ALL.SET_OF_BOOKS_ID,
  GL_JE_LINES.PERIOD_NAME,
  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,
  AR_DISTRIBUTIONS_ALL.ORG_ID,
  AR_DISTRIBUTIONS_ALL.SOURCE_ID,
  GL_JE_HEADERS.POSTED_DATE,
  DECODE(AR_ADJUSTMENTS_ALL.TYPE,'CHARGES','Charges Adjustments' ,'FREIGHT','Freight Adjustments' ,'INVOICE','Invoice Adjustments' ,'LINE','Line Adjustments' ,'TAX', 'Tax Adjustments'),
  AR_ADJUSTMENTS_ALL.RECEIVABLES_TRX_ID,
  AR_RECEIVABLES_TRX_ALL.NAME,
  AR_ADJUSTMENTS_ALL.ADJUSTMENT_NUMBER,
  RA_CUSTOMER_TRX_ALL.TRX_DATE,
  AR_DISTRIBUTIONS_ALL.SOURCE_TYPE,
  SUM(AR_DISTRIBUTIONS_ALL.AMOUNT_DR),
  SUM(AR_DISTRIBUTIONS_ALL.AMOUNT_CR),
  SUM(AR_DISTRIBUTIONS_ALL.ACCTD_AMOUNT_DR),
  SUM(AR_DISTRIBUTIONS_ALL.ACCTD_AMOUNT_CR),
  AR_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID,
  AR_DISTRIBUTIONS_ALL.CURRENCY_CODE,
  AR_DISTRIBUTIONS_ALL.THIRD_PARTY_ID,
  HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER,
  SUBSTR(HZ_PARTIES.PARTY_NAME,1,50),
  AR_ADJUSTMENTS_ALL.GL_DATE,
  RA_CUST_TRX_TYPES_ALL.DESCRIPTION,
  RA_CUSTOMER_TRX_ALL1.TRX_NUMBER,
  RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID,
  RA_CUSTOMER_TRX_ALL.TRX_NUMBER,
  RA_CUSTOMER_TRX_ALL.TRX_DATE,
  GL_JE_HEADERS.JE_CATEGORY,
  GL_JE_HEADERS.JE_SOURCE,
  GL_JE_HEADERS.NAME,
  GL_JE_LINES.REFERENCE_1,
  GL_JE_LINES.REFERENCE_3,
  GL_JE_LINES.STATUS,
  GL_JE_LINES.REFERENCE_5
FROM GL.GL_JE_LINES GL_JE_LINES,
  GL.GL_JE_HEADERS GL_JE_HEADERS,
  gl_import_references,
  XLA_AE_LINES,
  xla_ae_headers,
  GL_SETS_OF_BOOKS GL_SETS_OF_BOOKS,
  GL_JE_CATEGORIES_VL,
  XLA_DISTRIBUTION_LINKS,
  ar_distributions_all,
  AR.AR_ADJUSTMENTS_ALL AR_ADJUSTMENTS_ALL,
  AR.RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_ALL,
  AR.RA_CUST_TRX_TYPES_ALL RA_CUST_TRX_TYPES_ALL,
  AR.HZ_CUST_ACCOUNTS HZ_CUST_ACCOUNTS,
  AR.HZ_PARTIES HZ_PARTIES,
  AR.RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_ALL1,
  AR.AR_RECEIVABLES_TRX_ALL AR_RECEIVABLES_TRX_ALL
WHERE (1                      =1)
AND (GL_JE_HEADERS.JE_CATEGORY='Adjustment')
AND GL_JE_HEADERS.je_source   = 'Receivables'
AND (TRUNC(GL_JE_LINES.LAST_UPDATE_DATE) BETWEEN TO_DATE('01-OCT-2013','DD-MON-YYYY') AND NVL(null,TRUNC(SYSDATE+1)) )
AND (GL_JE_HEADERS.JE_HEADER_ID                         =GL_JE_LINES.JE_HEADER_ID)
AND gl_import_references.je_header_id                   = gl_je_lines.je_header_id
AND gl_import_references.je_line_num                    = gl_je_lines.je_line_num
AND (XLA_AE_LINES.GL_SL_LINK_ID                         =gl_import_references.GL_SL_LINK_ID)
AND xla_ae_lines.ae_header_id                           = xla_ae_headers.ae_header_id
AND xla_ae_headers.je_category_name                     = GL_JE_CATEGORIES_VL.je_category_name
AND (GL_JE_LINES.LEDGER_ID                              = GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID)
AND XLA_DISTRIBUTION_LINKS.EVENT_ID                     = xla_ae_headers.EVENT_ID
AND XLA_DISTRIBUTION_LINKS.AE_HEADER_ID                 = xla_ae_headers.AE_HEADER_ID
AND XLA_DISTRIBUTION_LINKS.AE_LINE_NUM                  = xla_ae_lines.ae_line_num
AND XLA_DISTRIBUTION_LINKS.source_distribution_id_num_1 = ar_distributions_all.line_id
AND XLA_DISTRIBUTION_LINKS.source_distribution_type     = 'AR_DISTRIBUTIONS_ALL'
AND ((AR_RECEIVABLES_TRX_ALL.RECEIVABLES_TRX_ID(+)      =AR_ADJUSTMENTS_ALL.RECEIVABLES_TRX_ID)
AND NVL(AR_ADJUSTMENTS_ALL.ORG_ID,-99)                  =NVL(AR_RECEIVABLES_TRX_ALL.ORG_ID(+),-99))
AND (HZ_CUST_ACCOUNTS.PARTY_ID                          =HZ_PARTIES.PARTY_ID)
AND (GL_JE_HEADERS.JE_HEADER_ID                         =GL_JE_LINES.JE_HEADER_ID)
AND ((RA_CUSTOMER_TRX_ALL1.CUSTOMER_TRX_ID(+)           =AR_ADJUSTMENTS_ALL.CHARGEBACK_CUSTOMER_TRX_ID)
AND NVL(RA_CUSTOMER_TRX_ALL1.ORG_ID(+),-99)             =NVL(AR_ADJUSTMENTS_ALL.ORG_ID,-99))
AND (HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID                   =AR_DISTRIBUTIONS_ALL.THIRD_PARTY_ID)
AND ((AR_DISTRIBUTIONS_ALL.SOURCE_ID                    =AR_ADJUSTMENTS_ALL.ADJUSTMENT_ID)
AND NVL(AR_DISTRIBUTIONS_ALL.ORG_ID,                                                    -99)               =NVL(AR_ADJUSTMENTS_ALL.ORG_ID,-99))
AND ((NVL(RA_CUST_TRX_TYPES_ALL.ORG_ID,99)              =NVL(RA_CUSTOMER_TRX_ALL.ORG_ID,-99))
AND RA_CUST_TRX_TYPES_ALL.CUST_TRX_TYPE_ID              =RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID)
AND ((RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID               =AR_ADJUSTMENTS_ALL.CUSTOMER_TRX_ID)
AND NVL(RA_CUSTOMER_TRX_ALL.ORG_ID,-99)                 =NVL(AR_ADJUSTMENTS_ALL.ORG_ID,-99))
GROUP BY GL_JE_LINES.JE_HEADER_ID,
  GL_JE_LINES.JE_LINE_NUM,
  AR_ADJUSTMENTS_ALL.SET_OF_BOOKS_ID,
  GL_JE_LINES.PERIOD_NAME,
  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,
  AR_DISTRIBUTIONS_ALL.ORG_ID,
  AR_DISTRIBUTIONS_ALL.SOURCE_ID,
  GL_JE_HEADERS.POSTED_DATE,
  DECODE(AR_ADJUSTMENTS_ALL.TYPE,'CHARGES','Charges Adjustments' ,'FREIGHT','Freight Adjustments' ,'INVOICE','Invoice Adjustments' ,'LINE','Line Adjustments' ,'TAX', 'Tax Adjustments'),
  AR_ADJUSTMENTS_ALL.RECEIVABLES_TRX_ID,
  AR_RECEIVABLES_TRX_ALL.NAME,
  AR_ADJUSTMENTS_ALL.ADJUSTMENT_NUMBER,
  RA_CUSTOMER_TRX_ALL.TRX_DATE,
  AR_DISTRIBUTIONS_ALL.SOURCE_TYPE,
  AR_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID,
  AR_DISTRIBUTIONS_ALL.CURRENCY_CODE,
  AR_DISTRIBUTIONS_ALL.THIRD_PARTY_ID,
  HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER,
  SUBSTR(HZ_PARTIES.PARTY_NAME,1,50),
  AR_ADJUSTMENTS_ALL.GL_DATE,
  RA_CUST_TRX_TYPES_ALL.DESCRIPTION,
  RA_CUSTOMER_TRX_ALL1.TRX_NUMBER,
  RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID,
  RA_CUSTOMER_TRX_ALL.TRX_NUMBER,
  RA_CUSTOMER_TRX_ALL.TRX_DATE,
  GL_JE_HEADERS.JE_CATEGORY,
  GL_JE_HEADERS.JE_SOURCE,
  GL_JE_HEADERS.NAME,
  GL_JE_LINES.REFERENCE_1,
  GL_JE_LINES.REFERENCE_3,
  GL_JE_LINES.STATUS,
  GL_JE_LINES.REFERENCE_5;