-- R12 SLA subledger and GL general ledger association query
Select
Xte. application_id "application ",
Xte. entity_id,
Xte. ledger_id "Ledger SOB ",
Xte. entity_code,
Xett. name "transaction entity type ",
Le. name "legal entity ",
Le. legal_entity_identifier "person subject Income Tax Registration" -- legal_entity_tax
------------------------------
-- AP_INVOICES AP invoice INVOICE_ID
-- AP_PAYMENTS AP payment CHECK_ID
-- RECEIPTS Collection CASH_RECEIPT_ID
-- TRANSACTIONS Transaction Processing sales invoice CUSTOMER_TRX_ID
,
Xte. source_id_int_1 "ID of the transaction source"
------------------------------
/* -- There will be a value for depreciation in the following two fields
, Xte. source_id_int_2
, Xte. source_id_int_3 */,
Xte. security_id_int_1 "ORG_ID ",
Xte. source_application_id "Source Application"
-- ====== Xla_event ========== --------
,
Xe. event_type_code -- Event type code
,
Xent. name "event type"
--, Xe. event_status_code -- Event status code
--, Xe. process_status_code -- Processing status code
/* This flag indicates whether the event is on hold or not.
Possible values: (Y) -- yes, (N) -- No */
--, Xe. on_hold_flag
-- ================ Xla_ AE _headers ======= -----
,
Xah. ledger_id "SOB ",
Xah. je_category_name -- General Ledger category name
,
Xah. accounting_date,
Xah. period_name "Period"
/*, Xah. balance_type_code -- Balance type (Actual, Budget, or Encumbrance)
, Xah. gl_transfer_date
, Xah. accounting_entry_status_code
, Xah. accounting_entry_type_code
, Xah. zero_amount_flag */
-- ================ Xla_ AE _line ======= -----
,
Xal. AE _line_num "row number ",
Xal. code_combination_id "account ID ",
Gjl. code_combination_id "journal gcc"
/*, Xal. gl_transfer_mode_code
, Xal. accounting_class_code "Accounting type "*/,
Xlp. meaning "accounting classification ",
Xal. accounted_dr "debit (base currency )",
Xal. accounted_cr "credit (base currency )",
Xal. currency_code "currency ",
Xal. entered_dr "account original currency debit ",
Xal. entered_cr "account original currency loan"
,
Gir. je_line_num "journal line number ",
Xdl. source_distribution_id_num_1 "source account Association ID ",
Xdl. tax_line_ref_id,
Xdl. unrounded_entered_dr,
Xdl. unrounded_entered_cr,
Xdl. applied_to_source_id_num_1
,
Xte. entity_id,
Xte. application_id,
Xe. event_id,
Xah. AE _header_id,
Xal. AE _line_num
From xla_transaction_entities xte,
Xla_entity_types_tl xett,
Xle_entity_profiles le,
Xla_events xe,
Xla_event_types_tl xent,
Xla_ AE _headers xah,
Xla_ AE _lines xal,
Xla_lookups xlp,
Xla_distribution_links xdl,
Gl_import_references gir,
Gl_je_lines gjl
Where 1 = 1
And xte. entity_id = xe. entity_id
And xte. application_id = xe. application_id
And xte. legal_entity_id = le. legal_entity_id (+) and xah. event_id = xe. event_id
And xah. application_id = xe. application_id
And xent. event_type_code = xe. event_type_code
And xent. application_id = xe. application_id
And xent. language = 'zhs'
And xah. AE _header_id = xal. AE _header_id
And xah. application_id = xal. application_id
And xlp. lookup_type (+) = 'xla _ ACCOUNTING_CLASS'
And xlp. lookup_code (+) = xal. accounting_class_code
And xal. AE _header_id = xdl. AE _header_id (+)
And xal. AE _line_num = xdl. AE _line_num (+)
And xal. application_id = xdl. application_id (+)
And gir. gl_sl_link_id = xal. gl_sl_link_id
And gir. gl_sl_link_table = xal. gl_sl_link_table
And gjl. je_header_id = gir. je_header_id
And gjl. je_line_num = gir. je_line_num
And xett. entity_code = xte. entity_code
And xett. application_id = xte. application_id
And xett. language = 'zhs ';
