Total Pageviews

July 3, 2016

7/03/2016 03:26:00 AM
Link between and XLA and AP tables

It is linked with GL_JE_BATCHES by JE_BATCH_ID and linked with GL_JE_HEADERS, GL_JE_LINES by JE_HEADER_ID 

This table is linked with XLA related table by reference column. 
REFERENCE_5 is ENTITY_ID in XLA table. 
REFERENCE_6 is ACCOUNTING_EVENT_ID in XLA table. 
REFERENCE_7 is AE_HEADER_ID in XLA table. 
The most important column in GL_IMPORT_REFERENCES is GL_SL_LINK_ID, you also can find this column in table XLA_AE_LINES 

The link in AP & GL is table GL_IMPORT_REFERENCES, key column is GL_SL_LINK_ID.

SELECT   aia.INVOICE_ID "Invoice Id",
         aia.INVOICE_NUM "Invoice Number",
         aia.INVOICE_DATE "Invoice Date",
         aia.INVOICE_AMOUNT "Amount",
         xal.ENTERED_DR "Entered DR in SLA",
         xal.ENTERED_CR "Entered CR in SLA",
         xal.ACCOUNTED_DR "Accounted DR in SLA",
         xal.ACCOUNTED_CR "Accounted CR in SLA",
         gjl.ENTERED_DR "Entered DR in GL",
         gjl.ACCOUNTED_DR "Accounted DR in GL",
         xal.ACCOUNTING_CLASS_CODE "Accounting Class",
         aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
         aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
         aia.GL_DATE "GL Date",
         xah.PERIOD_NAME "Period",
         aia.PAYMENT_METHOD_CODE "Payment Method",
         xah.JE_CATEGORY_NAME "JE Category Name"
  FROM   ap.ap_invoices_all aia,
         xla.xla_transaction_entities XTE,
         xla.xla_events xev,
         xla.xla_ae_headers XAH,
         xla.xla_ae_lines XAL,
         GL_IMPORT_REFERENCES gir,
         gl_je_headers gjh,
         gl_je_lines gjl,
         gl_code_combinations gcc
 WHERE       aia.INVOICE_ID = xte.source_id_int_1
         AND xev.entity_id = xte.entity_id
         AND xah.entity_id = xte.entity_id
         AND xah.event_id = xev.event_id
         AND XAH.ae_header_id = XAL.ae_header_id
         AND XAH.je_category_name = 'Purchase Invoices'
         AND XAH.gl_transfer_status_code = 'Y'
         AND XAL.GL_SL_LINK_ID = gir.GL_SL_LINK_ID
         AND gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
         AND gjl.JE_HEADER_ID = gjh.JE_HEADER_ID
         AND gjh.JE_HEADER_ID = gir.JE_HEADER_ID
         AND gjl.JE_HEADER_ID = gir.JE_HEADER_ID
         AND gir.JE_LINE_NUM = gjl.JE_LINE_NUM
         AND gcc.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID
         AND gcc.CODE_COMBINATION_ID = gjl.CODE_COMBINATION_ID
         AND gjh.STATUS = 'P'
         AND gjh.Actual_flag = 'A'
         AND gjh.CURRENCY_CODE = 'USD'
         AND aia.Invoice_id = &Invoice_Id;


Where &Invoice_Id can be identified from XLA_TRANSACTION_ENTITIES.source_id_int_1 in order to verify the link.


=========================================================================
AR->SLA->GL
=========================================================================
ACTION: COMPLETE TRANSACTION:

RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID
RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1
XLA_TRANSACTION_ENTITIES.ENTITY_ID = XLA_EVENTS.ENTITY_ID

ACTION: RUN CREATE ACCOUNTING:

RA_CUST_TRX_LINE_GL_DIST_ALL.CUST_TRX_LINE_GL_DIST_ID = XLA_DISTRIBUTION_LINKS.SOURCE_DISTRIBUTION_ID_NUM_1
XLA_AE_LINES.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID
XLA_AE_HEADERS.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID

ACTION: RUN TRANSFER TO GL:

XLA_AE_LINES.GL_SL_LINE_ID = GL_JE_LINES.GL_SL_LINK_ID
XLA_AE_LINES.GL_SL_LINK_ID = GL_IMPORT_REFERENCES.GL_SL_LINK_ID
GL_IMPORT_REFERENCES.JE_HEADER_ID = GL_JE_LINES.JE_HEADER_ID
GL_IMPORT_REFERENCES.HE_HEADER_ID = GL_JE_HEADERS.JE_HEADER_ID

SELECT   GLB.NAME batch_name,
         GLB.description batch_description,
         gjh.je_category,
         gjh.je_source,
         gjh.period_name je_period_name,
         gjh.NAME journal_name,
         gjh.status journal_status,
         gjh.description je_description,
         gjl.je_line_num line_number
  FROM   gl_je_batches GLB,
         gl_je_headers gjh,
         gl_je_lines gjl,
         gl_code_combinations_kfv glcc,
         gl_import_references gir,
         xla_ae_lines xlal,
         xla_ae_headers xlah,
         xla_events xlae,
         xla_transaction_entities xlate,
         ra_customer_trx_all rct
 WHERE       GLB.je_batch_id = gjh.je_batch_id
         AND gjh.je_header_id = gjl.je_header_id
         AND xlal.code_combination_id = glcc.code_combination_id
         AND gjl.je_header_id = gir.je_header_id
         AND gjl.je_line_num = gir.je_line_num
         AND gir.gl_sl_link_table = xlal.gl_sl_link_table
         AND gir.gl_sl_link_id = xlal.gl_sl_link_id
         AND xlal.ae_header_id = xlah.ae_header_id
         AND xlah.event_id = xlae.event_id
         AND xlae.entity_id = xlate.entity_id
         AND xlae.application_id = xlate.application_id
         AND gjh.je_source = 'Receivables'
         AND rct.trx_number = xlate.transaction_number
         AND xlate.transaction_number = &p_trx_num;

 
Related Posts Plugin for WordPress, Blogger...