Oracle EBS SLA取值

-- 从GL总账追溯到 => 子分类账SLA => 子模块AP、AR等
SELECT xep.name, -- 法人主体
       xep.legal_entity_identifier, -- 法人主体所得纳税税登记
       --
       xentity_t.entity_code,
       xentity_t.name "事务实体类型名称",
       --
       xte.application_id "应用ID",
       xte.entity_id,
       xte.ledger_id      "分类账SOB ID",
       ------------------------------       
       --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 "源应用ID",
       --
       xe.event_id,
       xe.event_type_code,
       xevent_t.name "事件类型",
       --
       --==============xla_ae_headers=======-----
       xah.ae_header_id,
       xah.ledger_id "SOB ID",
       xah.je_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 "日记帐行号",
       
       --==============xla_distribution_links=======-----
       xdl.source_distribution_type,  -- 源账户分配类型
       xdl.source_distribution_id_num_1 "源账户关联ID",  -- Transaction source distribution identifer (Number) 
       xdl.tax_line_ref_id,  -- 税明细行 ZX_LINES_V.TAX_LINE_ID  -- Detail Tax Line Reference
       xdl.unrounded_entered_dr,  -- Unrounded Entered Debit Amount for the journal line (DR)
       xdl.unrounded_entered_cr,  -- Unrounded Entered Credit Amount for the journal line (CR)
               --
       xdl.applied_to_distribution_type,  -- (如：AP_INV_DIST、PO_DISTRIBUTIONS_ALL等)
       xdl.applied_to_source_id_num_1,  -- (如：核销行 源发票ID 、PO_DISTRIBUTION_ID等)
       xdl.applied_to_dist_id_num_1 
       --
  FROM xle_entity_profiles xep,
       --
       xla_entity_types_tl xentity_t,
       --
       xla.xla_transaction_entities xte, -- ORG VPD
       xla_event_types_tl           xevent_t,
       xla_events                   xe,
       --
       xla_distribution_links xdl, -- distribution
       --
       xla_ae_headers xah,
       xla_ae_lines   xal,
       xla_lookups    xlp,
       -- 
       gl_import_references gir,
       gl_je_headers        gjh,
       gl_je_lines          gjl
 WHERE 1 = 1
      -- 1.9 xep + xte
   AND xep.legal_entity_id(+) = xte.legal_entity_id
      -- 1.8 xentity_t + xte
   AND xentity_t.entity_code = xte.entity_code
   AND xentity_t.application_id = xte.application_id
   AND xentity_t.language = userenv('LANG')
      -- 1.7 xevent_t + xe
   AND xevent_t.event_type_code = xe.event_type_code
   AND xevent_t.application_id = xe.application_id
   AND xevent_t.language = userenv('LANG')
      -- 1.6 xte + xe/xah
      -- XLA.XLA_TRANSACTION_ENTITIES_U1 on XLA.XLA_TRANSACTION_ENTITIES (ENTITY_ID, APPLICATION_ID)
      -- 如果不关联xla_events表，则此处可以使用如下方式进行关联
      -- AND xte.entity_id = xah.entity_id
      -- AND xte.application_id = xah.application_id
   AND xte.entity_id = xe.entity_id
   AND xte.application_id = xe.application_id
      -- 1.5 xe + xah 
      -- XLA.XLA_EVENTS_U1 on XLA.XLA_EVENTS (EVENT_ID, APPLICATION_ID)
      -- XLA.XLA_EVENTS_U2 on XLA.XLA_EVENTS (ENTITY_ID, EVENT_NUMBER, APPLICATION_ID)
   AND xe.event_id = xah.event_id
   AND xe.application_id = xah.application_id
      
      -- 1.4 xal + xdl(distribution)
      -- 此处为什么使用外连接 ? 因为有的事务处理没有distribution ^_^
   AND xdl.ae_header_id(+) = xal.ae_header_id
   AND xdl.ae_line_num(+) = xal.ae_line_num
   AND xdl.application_id(+) = xal.application_id
      -- 1.3 xah + xal
   AND xah.ae_header_id = xal.ae_header_id
   AND xah.application_id = xal.application_id
   AND xlp.lookup_code(+) = xal.accounting_class_code
   AND xlp.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
      -- 1.2 xal + gir
   AND xal.gl_sl_link_id = gir.gl_sl_link_id
   AND xal.gl_sl_link_table = gir.gl_sl_link_table
      -- 1.1 gir + gl
   AND gir.je_header_id = gjh.je_header_id
   AND gir.je_line_num = gjl.je_line_num
      -- 
   AND gjh.je_header_id = gjl.je_header_id
   AND gjh.je_header_id = 216128 /*216124*/
-- AND gjl.je_line_num = 1, 2, 3

　　

--应收事务处理追溯SLA
SELECT pv.vendor_name,
       sum(rt.quantity) quantity,
       sum(xal.accounted_dr) accounted_dr,
       sum(xal.accounted_cr) accounted_cr
  FROM xla_ae_lines             xal,
       gl_code_combinations_v   gcc,
       xla_ae_headers           xae,
       rcv_receiving_sub_ledger rrsl,
       xla_distribution_links   xdl,
       rcv_transactions         rt,
       po_vendors               pv
 WHERE 1 = 1
   AND xal.code_combination_id = gcc.code_combination_id
   AND rrsl.code_combination_id = xal.code_combination_id
   AND gcc.segment3 = '22020201'
   AND xae.ae_header_id = xal.ae_header_id
   AND xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id
   AND xdl.ae_header_id = xal.ae_header_id
   AND rt.transaction_id = rrsl.rcv_transaction_id
   AND rt.vendor_id = pv.vendor_id
   AND xae.period_name = '01-15'
   GROUP BY pv.vendor_name;

　　

--应付发票追溯SLA
SELECT jh.name 总账日记账名称
       jh.je_source 总账日记账来源
       jh.je_category 总账日记账分类
       jh.description 总账日记账说明
       jh.currency_code 总账币别
       jh.period_name 总账会计期间
       jh.default_effective_date 总账有效日期
       jl.je_line_num 总账行号
       gcc.concatenated_segments 总账账户
       jl.accounted_dr 总账入账借方
       jl.accounted_cr 总账入账贷方
       jl.description 总账行说明
       xal.ae_line_num 分类账行号
       xal.accounted_dr 分类账借方金额
       xal.accounted_cr 分类账贷方金额
       nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0) 分类账余额
       xal.description 分类账行说明
       su.vendor_name 供应商名称
       sus.vendor_site_code 供应商地点
       ai.invoice_num 发票或付款编号
       ai.invoice_date 发票日期
  FROM gl_je_headers                jh,
       gl_je_lines                  jl,
       gl_import_references         gir,
       gl_code_combinations_kfv     gcc,
       xla_ae_lines                 xal,
       xla_ae_headers               xah,
       xla.xla_transaction_entities xte,
       ap.ap_invoices_all           ai,
       ap.ap_suppliers              su,
       ap.ap_supplier_sites_all     sus
 WHERE jh.je_header_id = jl.je_header_id
   AND jl.je_header_id = gir.je_header_id
   AND jl.je_line_num = gir.je_line_num
   AND jl.code_combination_id = gcc.code_combination_id
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gir.gl_sl_link_table = xal.gl_sl_link_table
   AND xal.application_id = xah.application_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xah.application_id = xte.application_id
   AND xah.entity_id = xte.entity_id
   AND xte.source_id_int_1 = ai.invoice_id
   AND xte.entity_code = 'AP_INVOICES'
   AND ai.vendor_id = su.vendor_id
   AND ai.vendor_id = sus.vendor_id
   AND ai.vendor_site_id = sus.vendor_site_id
   AND ai.org_id = sus.org_id
   AND xal.displayed_line_number > 0
   AND jh.ACTUAL_FLAG = 'A'
      --AND su.vendor_name = '&供应商名称
  -- AND gcc.segment1 like 'A140101'
  AND gcc.code_combination_id = 31322
      --AND gcc.segment2 = '&部门
      -- AND gcc.segment3 = '&会计科目
   AND jl.period_name = '2015-07'

　　

--收款追溯SLA
SELECT hp.party_name
      ,(nvl(al.accounted_cr
           ,0) - nvl(al.accounted_dr
                     ,0)) amount
      ,crh_first_posted.gl_date
      ,cr.comments
  FROM xla.xla_ae_lines             al
      ,xla.xla_ae_headers           ah
      ,xla.xla_transaction_entities xte
      ,ar_cash_receipts_all         cr
      ,ar_cash_receipt_history_all  crh_first_posted
      ,hz_cust_site_uses_all        csu
      ,hz_cust_acct_sites_all       hcas
      ,hz_cust_accounts_all         hca
      ,hz_parties                   hp

 WHERE 1 = 1
   AND al.ae_header_id = ah.ae_header_id
   AND ah.entity_id = xte.entity_id
   AND xte.entity_code = 'RECEIPTS'
   AND cr.cash_receipt_id = xte.source_id_int_1
   AND ah.ledger_id = 2021
   AND al.code_combination_id = 20795
   AND hp.party_id = hca.party_id
   AND csu.site_use_id = cr.customer_site_use_id
   AND csu.cust_acct_site_id = hcas.cust_acct_site_id
   AND hca.cust_account_id = hcas.cust_account_id
   AND hcas.org_id = cr.org_id
   AND crh_first_posted.cash_receipt_id(+) = cr.cash_receipt_id
   AND crh_first_posted.org_id(+) = cr.org_id
   AND crh_first_posted.first_posted_record_flag(+) = 'Y'
 ORDER BY hp.party_name

　　

--库存事务处理
SELECT pv.vendor_name,
       poh.segment1,
       mmt.transaction_quantity,
       rt.quantity,
       mmt.rcv_transaction_id,
       mmt.transaction_type_id,
       xal.accounted_dr,
       xal.accounted_cr,
       OOD.ORGANIZATION_NAME
  FROM mtl_material_transactions    mmt,
       rcv_transactions             rt,
       po_headers_all               poh,
       po_vendors                   pv,
       xla_ae_lines                 xal,
       gl_code_combinations_v       gcc,
       xla_ae_headers               xah,
       xla.xla_events               xe,
       xla.xla_transaction_entities xte,
       org_organization_definitions ood
 WHERE 1 = 1
   AND xal.code_combination_id = gcc.code_combination_id
   AND gcc.segment3 = '22020201'
   AND xal.ae_header_id = xah.ae_header_id
   AND xah.event_id = xe.event_id
   AND xe.entity_id = xte.entity_id
   AND xte.entity_code = 'MTL_ACCOUNTING_EVENTS'
   AND mmt.transaction_id = xte.source_id_int_1
   AND mmt.rcv_transaction_id = rt.transaction_id(+)
   AND rt.po_header_id = poh.po_header_id(+)
   AND poh.vendor_id = pv.vendor_id(+)
   AND ood.organization_id = mmt.organization_id
   AND to_char(mmt.transaction_date, 'YYYY-MM') = '2015-02';

　　

--付款
-- 从应付付款追溯
SELECT jh.name 总账日记账名称
       jh.je_source 总账日记账来源
       jh.je_category 总账日记账分类
       jh.description 总账日记账说明
       jh.currency_code 总账币别
       jh.period_name 总账会计期间
       jh.default_effective_date 总账有效日期
       jl.je_line_num 总账行号
       gcc.concatenated_segments 总账账户
       jl.accounted_dr 总账入账借方
       jl.accounted_cr 总账入账贷方
       jl.description 总账行说明
       xal.ae_line_num 分类账行号
       xal.accounted_dr 分类账借方金额
       xal.accounted_cr 分类账贷方金额
       nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0) 分类账余额
       xal.description 分类账行说明
       su.vendor_name 供应商名称
       sus.vendor_site_code 供应商地点
       to_char(ac.check_number) 付款或付款编号
       ac.check_date 发票或付款日期
  FROM gl_je_headers                jh,
       gl_je_lines                  jl,
       gl_import_references         gir,
       gl_code_combinations_kfv     gcc,
       xla_ae_lines                 xal,
       xla_ae_headers               xah,
       xla.xla_transaction_entities xte,
       ap.ap_checks_all             ac,
       ap.ap_suppliers              su,
       ap.ap_supplier_sites_all     sus
 WHERE jh.je_header_id = jl.je_header_id
   AND jl.je_header_id = gir.je_header_id
   AND jl.je_line_num = gir.je_line_num
   AND jl.code_combination_id = gcc.code_combination_id
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gir.gl_sl_link_table = xal.gl_sl_link_table
   AND xal.application_id = xah.application_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xah.application_id = xte.application_id
   AND xah.entity_id = xte.entity_id
   AND xte.source_id_int_1 = ac.check_id
   AND xte.entity_code = 'AP_PAYMENTS'
   AND ac.vendor_id = su.vendor_id
   AND ac.vendor_id = sus.vendor_id
   AND ac.vendor_site_id = sus.vendor_site_id
   AND ac.org_id = sus.org_id
   AND xal.displayed_line_number > 0
   AND jh.ACTUAL_FLAG = 'A'
      --AND su.vendor_name = '&供应商名称
  -- AND gcc.segment1 like 'A14%'
  AND gcc.code_combination_id = 31322
      --AND gcc.segment2 = '&部门
      --AND gcc.segment3 = '&会计科目
   AND jl.period_name = '2015-07'

　　

--采购
SELECT jh.name 总账日记账名称
       jh.je_source 总账日记账来源
       jh.je_category 总账日记账分类
       jh.description 总账日记账说明
       jh.currency_code 总账币别
       jh.period_name 总账会计期间
       jh.default_effective_date 总账有效日期
       jl.je_line_num 总账行号
       gcc.concatenated_segments 总账账户
       jl.accounted_dr 总账入账借方
       jl.accounted_cr 总账入账贷方
       jl.description 总账行说明
       xal.ae_line_num 分类账行号
       xal.accounted_dr 分类账借方金额
       xal.accounted_cr 分类账贷方金额
       nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0) 分类账余额
       xal.description 分类账行说明
       su.vendor_name 供应商名称
       sus.vendor_site_code 供应商地点
       to_char(ac.check_number) 付款或付款编号
       ac.check_date 发票或付款日期
  FROM gl_je_headers                jh,
       gl_je_lines                  jl,
       gl_import_references         gir,
       gl_code_combinations_kfv     gcc,
       xla_ae_lines                 xal,
       xla_ae_headers               xah,
       xla.xla_transaction_entities xte,
       ap.ap_checks_all             ac,
       ap.ap_suppliers              su,
       ap.ap_supplier_sites_all     sus
 WHERE jh.je_header_id = jl.je_header_id
   AND jl.je_header_id = gir.je_header_id
   AND jl.je_line_num = gir.je_line_num
   AND jl.code_combination_id = gcc.code_combination_id
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gir.gl_sl_link_table = xal.gl_sl_link_table
   AND xal.application_id = xah.application_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xah.application_id = xte.application_id
   AND xah.entity_id = xte.entity_id
   AND xte.source_id_int_1 = ac.check_id
   AND xte.entity_code = 'AP_PAYMENTS'
   AND ac.vendor_id = su.vendor_id
   AND ac.vendor_id = sus.vendor_id
   AND ac.vendor_site_id = sus.vendor_site_id
   AND ac.org_id = sus.org_id
   AND xal.displayed_line_number > 0
   AND jh.ACTUAL_FLAG = 'A'
      --AND su.vendor_name = '&供应商名称
  -- AND gcc.segment1 like 'A14%'
  AND gcc.code_combination_id = 31322
      --AND gcc.segment2 = '&部门
      --AND gcc.segment3 = '&会计科目
   AND jl.period_name = '2015-07'

　　

转载于:https://www.cnblogs.com/jenrry/p/10020780.html