GL source_id_int_1 Mappings

The source_id_int_1 on the xla_transaction_entities table can be used to join from SLA to related transactions in the sub-ledgers.
There is a very useful table called xla_entity_id_mappings which contains the mapping information.
This covers the mappings used for some common transaction types:

APPLICATION_ID		APPLICATION_NAME	ENTITY_CODE	SOURCE_ID_COL_NAME_1	TRANSACTION_ID_COL_NAME_1	SOURCE_ID_COL_NAME_2	TRANSACTION_ID_COL_NAME_2
200		Payables	AP_INVOICES	SOURCE_ID_INT_1	INVOICE_ID
200		Payables	AP_PAYMENTS	SOURCE_ID_INT_1	CHECK_ID
275		Projects	BUDGETS	SOURCE_ID_INT_1	BUDGET_VERSION_ID
275		Projects	EXPENDITURES	SOURCE_ID_INT_1	EXPENDITURE_ITEM_ID
275		Projects	REVENUE	SOURCE_ID_INT_1	PROJECT_ID	SOURCE_ID_INT_2	DRAFT_REVENUE_NUM
201		Purchasing	PURCHASE_ORDER	SOURCE_ID_INT_1	PO_HEADER_ID
201		Purchasing	REQUISITION	SOURCE_ID_INT_1	REQ_ID
222		Receivables	RECEIPTS	SOURCE_ID_INT_1	CASH_RECEIPT_ID
222		Receivables	TRANSACTIONS	SOURCE_ID_INT_1	CUSTOMER_TRX_ID

For example, for a Revenue in Projects, you could see that accounting info that flows from Projects through to GL would have and source_id_int_1 value related to the PROJECT_ID and a source_id_int_2 value related to the DRAFT_REVENUE_NUM.

Having access to the xla_entity_id_mappings helps take the guess work out of linking source transactions to the sub ledger tables.

-- ##############################################################################

--       SOURCE_ID_INT_1 MAPPINGS

-- ############################################################################*/

     

-- source_id_int_1 mappings

-- for a given application, find out what entity (e.g. AP_INVOICES) and column_name (e.g. INVOICE_ID)

-- found via Application Diagnostics > Subledger Accounting > Setup Diagnostics > Xla Setup Diagnostics Diagnostics Script

 

    SELECT fa.application_id

         , fa.application_short_name app

         , fat.application_name

         , DECODE(fpi.status,'I','Licensed','S','Shared','N','Not Licensed') status

         , fpi.product_version

         , fpi.patch_level

         , xeim.entity_code

         , xeim.source_id_col_name_1

         , xeim.transaction_id_col_name_1

         , xeim.source_id_col_name_2

         , xeim.transaction_id_col_name_2

         , xeim.source_id_col_name_3

         , xeim.transaction_id_col_name_3

         , xeim.source_id_col_name_4

         , xeim.transaction_id_col_name_4

      FROM xla.xla_entity_id_mappings xeim

      JOIN applsys.fnd_application fa ON xeim.application_id = fa.application_id

      JOIN applsys.fnd_application_tl fat ON fa.application_id = fat.application_id

 LEFT JOIN applsys.fnd_product_installations fpi ON fpi.application_id = fa.application_id

     WHERE 1 = 1

       AND xeim.source_id_col_name_1 IS NOT NULL

--       AND fpi.status <> 'N'

  ORDER BY fat.application_name

         , xeim.entity_code;

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;

QUERY FOR AP - XLA - GL RELATION IN EBS R12

SELECT GL_JE_HEADERS.JE_HEADER_ID,

  GL_JE_LINES.JE_LINE_NUM,

  xla_ae_headers.ledger_id,

  GL_JE_LINES.PERIOD_NAME,

  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,

  AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID,

  xla_ae_lines.SOURCE_ID,

  GL_JE_HEADERS.POSTED_DATE,

  AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE,

  AP_LOOKUP_CODES.DISPLAYED_FIELD,

  AP_INVOICES_ALL.INVOICE_NUM,

  AP_INVOICES_ALL.INVOICE_DATE,

  SUM(xla_ae_lines.ENTERED_DR),

  SUM(xla_ae_lines.ENTERED_CR),

  SUM(xla_ae_lines.ACCOUNTED_DR),

  SUM(xla_ae_lines.ACCOUNTED_CR),

  GL_JE_LINES.CODE_COMBINATION_ID,

  GL_JE_HEADERS.CURRENCY_CODE,

  PO_VENDORS.SEGMENT1,

  PO_VENDORS.VENDOR_NAME,

  xla_ae_headers.ACCOUNTING_DATE,

  AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER,

  AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE,

  PO_HEADERS_ALL.SEGMENT1,

  AP_INVOICES_ALL.INVOICE_NUM,

  PO_RELEASES_ALL.RELEASE_NUM,

  RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE,

  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,

  GL_JE_LINES.LAST_UPDATE_DATE

FROM xla_ae_headers,

  xla_ae_lines,

  AP_ACCOUNTING_EVENTS_ALL,

  AP_INVOICES_ALL,

  AP_LOOKUP_CODES,

  AP_INVOICE_DISTRIBUTIONS_ALL,

  PO_DISTRIBUTIONS_ALL,

  RCV_TRANSACTIONS RCV_TRANSACTIONS1,

  PO_RELEASES_ALL,

  PO_HEADERS_ALL,

  RCV_SHIPMENT_HEADERS,

  GL_JE_CATEGORIES_VL,

  gl_import_references,

  gl_je_lines,

  GL_JE_HEADERS,

  GL_SETS_OF_BOOKS,

  PO_VENDORS

WHERE 1=1

  --AND xla_ae_headers.je_category_name IN ('Payments','Purchase Invoices','Reconciled Payments'

AND GL_JE_HEADERS.JE_HEADER_ID=173521

AND TRUNC(gl_je_lines.last_update_date) BETWEEN TO_DATE('01-MAY-2014','DD-MON-YYYY')

AND TO_DATE('31-MAY-2014','DD-MON-YYYY')

AND xla_ae_lines.ae_header_id                                                                = xla_ae_headers.ae_header_id

AND (AP_ACCOUNTING_EVENTS_ALL.SOURCE_TABLE                                                   = 'AP_INVOICES')

AND (AP_ACCOUNTING_EVENTS_ALL.ACCOUNTING_EVENT_ID                                            =xla_ae_headers.EVENT_ID)

AND (AP_ACCOUNTING_EVENTS_ALL.SOURCE_ID                                                      = AP_INVOICES_ALL.INVOICE_ID)

AND (AP_LOOKUP_CODES.LOOKUP_CODE                                                             =AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE)

AND (AP_LOOKUP_CODES.LOOKUP_TYPE                                                             ='INVOICE TYPE')

AND (DECODE(XLA_AE_LINES.SOURCE_TABLE,'AP_INVOICE_DISTRIBUTIONS',XLA_AE_LINES.SOURCE_ID,NULL)=AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID(+))

AND (AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID                                         =PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+))

AND (AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID                                         =RCV_TRANSACTIONS1.TRANSACTION_ID(+))

AND (PO_DISTRIBUTIONS_ALL.PO_RELEASE_ID                                                      =PO_RELEASES_ALL.PO_RELEASE_ID(+))

AND (PO_DISTRIBUTIONS_ALL.PO_HEADER_ID                                                       =PO_HEADERS_ALL.PO_HEADER_ID(+))

AND (RCV_TRANSACTIONS1.SHIPMENT_HEADER_ID                                                    =RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID(+))

AND (XLA_AE_HEADERS.JE_CATEGORY_NAME                                                         = GL_JE_CATEGORIES_VL.JE_CATEGORY_NAME)

AND (XLA_AE_LINES.GL_SL_LINK_ID                                                              =gl_import_references.GL_SL_LINK_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 (GL_JE_HEADERS.JE_HEADER_ID                                                              = GL_JE_LINES.JE_HEADER_ID)

AND (GL_JE_LINES.LEDGER_ID                                                                   =GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID)

AND (XLA_AE_LINES.PARTY_ID                                                                   = PO_VENDORS.VENDOR_ID)

GROUP BY GL_JE_HEADERS.JE_HEADER_ID,

  GL_JE_LINES.JE_LINE_NUM,

  xla_ae_headers.ledger_id,

  GL_JE_LINES.PERIOD_NAME,

  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,

  AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID,

  xla_ae_lines.SOURCE_ID,

  GL_JE_HEADERS.POSTED_DATE,

  AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE,

  AP_LOOKUP_CODES.DISPLAYED_FIELD,

  AP_INVOICES_ALL.INVOICE_NUM,

  AP_INVOICES_ALL.INVOICE_DATE,

  GL_JE_LINES.CODE_COMBINATION_ID,

  GL_JE_HEADERS.CURRENCY_CODE,

  PO_VENDORS.SEGMENT1,

  PO_VENDORS.VENDOR_NAME,

  xla_ae_headers.ACCOUNTING_DATE,

  AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER,

  AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE,

  PO_HEADERS_ALL.SEGMENT1,

  AP_INVOICES_ALL.INVOICE_NUM,

  PO_RELEASES_ALL.RELEASE_NUM,

  RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE,

  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,

  GL_JE_LINES.LAST_UPDATE_DATE ;