ORACLE EBS 应付模块向SL子分类帐、GL追溯

--应付
select aia.invoice_num 发票编号,
       sup.segment1,
       aia.org_id,
       /* aia.attribute5 是否单边,
       aia.voucher_num 凭证编号,
       sup.vendor_name 供应商名称,
       xal.accounting_date 入账日期,
       xal.description 摘要,*/
       xal.accounted_dr 借方,
       xal.accounted_cr 贷方,
       /*   gcc.segment1 公司,
       gcc.segment2 部门,
       gl_flexfields_pkg.get_description_sql(101, 2, gcc.segment2) 部门,*/
       gcc.segment3 会计科目,
       gl_flexfields_pkg.get_description_sql(101, 3, gcc.segment3) 会计科目,
       xah.ae_header_id,
       xah.event_type_code,
       xal.accounting_class_code
  from xla.xla_ae_headers           xah,
       xla.xla_ae_lines             xal,
       gl_code_combinations         gcc,
       ap_invoices_all              aia,
       ap_suppliers                 sup,
       xla.xla_transaction_entities xte
 where xah.ae_header_id = xal.ae_header_id
       and xah.application_id = 200
       and xal.code_combination_id = gcc.code_combination_id
      /* AND gcc.segment1 = '1003'
      AND gcc.segment3 LIKE '6%'  
      AND xah.ledger_id = 2022
      AND xah.accounting_date BETWEEN to_date('2015-01-01', 'YYYY-MM-DD') AND
          to_date('2015-03-31', 'YYYY-MM-DD')*/
       and xte.application_id = 200
       and xte.entity_id = xah.entity_id
       and xte.entity_code = 'AP_INVOICES'
       and xte.source_id_int_1 = aia.invoice_id
      --AND aia.org_id = 87
       and sup.vendor_id = aia.vendor_id
       and aia.invoice_num in ('1002210300027GUORR')
 order by xal.ae_header_id, xal.ae_line_num;


--付款
select ac.check_voucher_num 凭证编号,
       sup.vendor_name 供应商名称,
       xal.accounting_date 入账日期,
       xal.description 摘要,
       xal.accounted_dr 借方,
       xal.accounted_cr 贷方,
       gcc.segment1 公司,
       gcc.segment2 部门,
       gl_flexfields_pkg.get_description_sql(101, 2, gcc.segment2) 部门,
       gcc.segment3 会计科目,
       gl_flexfields_pkg.get_description_sql(101, 3, gcc.segment3) 会计科目,
       gcc.segment5 业务线,
       gl_flexfields_pkg.get_description_sql(101, 5, gcc.segment5) 业务线
  from xla.xla_ae_headers           xah,
       xla.xla_ae_lines             xal,
       gl_code_combinations         gcc,
       ap_checks_all                ac,
       ap_suppliers                 sup,
       xla.xla_transaction_entities xte,
       ap_invoice_payments_all      ap,
       ap_invoices_all              aia
 where xah.ae_header_id = xal.ae_header_id
       and xah.application_id = 200
       and xal.code_combination_id = gcc.code_combination_id
      /* AND gcc.segment1 = '1003'
      --AND gcc.segment3 LIKE '6%'  
      AND xah.ledger_id = 2022
      AND xah.accounting_date BETWEEN to_date('2015-09-01', 'YYYY-MM-DD') AND
          to_date('2015-09-30', 'YYYY-MM-DD')*/
       and xte.application_id = 200
       and xte.entity_id = xah.entity_id
       and xte.entity_code = 'AP_PAYMENTS'
       and xte.source_id_int_1 = ac.check_id
      --AND ac.org_id = 87
       and sup.vendor_id = ac.vendor_id
       and ac.check_id = ap.check_id
       and ap.invoice_id = aia.invoice_id
       and aia.invoice_num = '11014191100019'
 order by xal.entered_dr;