--R12 SLA 子分類帳和GL總帳關聯查詢
select
xte.application_id "應用",
xte.entity_id,
xte.ledger_id "分類帳SOB",
xte.entity_code,
xett.name "事務實體類型",
le.name "法人主體",
le.legal_entity_identifier "人主體所得稅納稅登記" --legal_entity_tax
------------------------------
--AP_INVOICES AP發票 INVOICE_ID
--AP_PAYMENTS AP付款 CHECK_ID
--RECEIPTS 收款 CASH_RECEIPT_ID
--TRANSACTIONS 交易處理 銷售發票 CUSTOMER_TRX_ID
,
xte.source_id_int_1 "事務源對應ID"
------------------------------
/*--下面兩個欄位折舊的時候會有值
,xte.source_id_int_2
,xte.source_id_int_3*/,
xte.security_id_int_1 "ORG_ID",
xte.source_application_id "源對應應用"
--======xla_event=========--------
,
xe.event_type_code --Event type code
,
xent.name "事件類型"
-- ,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 "期間"
/* ,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 "行號",
xal.code_combination_id "賬戶ID",
gjl.code_combination_id "日記帳gcc"
/* ,xal.gl_transfer_mode_code
,xal.accounting_class_code "會計分類"*/,
xlp.meaning "會計分類",
xal.accounted_dr "入賬借項(本位幣)",
xal.accounted_cr "入賬貸項(本位幣)",
xal.currency_code "幣種",
xal.entered_dr "賬戶原幣借項",
xal.entered_cr "賬戶原幣貸項"
,
gir.je_line_num "帳目明細號",
xdl.source_distribution_id_num_1 "源賬戶關聯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';
