日记账GL追溯到AR模块

--日记账AR模块数据，追溯到IMS（IMS数据）
SELECT 
       gl.ledger_id, --帐套ID
       gl.name ledger_name, --账套
       gjh.period_name period_name, --期间
       gcc.segment1 segment1, --公司段
       gcc.segment3 segment3, --科目段
       gcc.segment7 segment7, --渠道
       chan_val.description chanel_name, --渠道值
       gjh.currency_code currency_code, --币种
       SUM(nvl(gjl.entered_dr, 0) - nvl(gjl.entered_cr, 0)) amount --金额
  FROM gl_je_headers                gjh, --凭证头
       gl_je_lines                  gjl, --凭证行
       gl_import_references         gi,
       xla_ae_headers               xah,
       xla_ae_lines                 xal,
       xla.xla_transaction_entities xte,
       gl_code_combinations         gcc,
       fnd_flex_values_vl           chan_val,
       fnd_flex_value_sets          chan_vs,
       ra_customer_trx_all          rcta, --应收发票
       ra_batch_sources_all         bs,
       gl_ledgers                   gl,
       cux_all_org_information_v    cao
 WHERE gjh.je_header_id = gjl.je_header_id
   AND gjh.je_source = 'Receivables'
   AND gjl.je_header_id = gi.je_header_id
   AND gjl.je_line_num = gi.je_line_num
   AND gi.gl_sl_link_id = xal.gl_sl_link_id
   AND gi.gl_sl_link_table = xal.gl_sl_link_table
   AND xah.ae_header_id = xal.ae_header_id
   AND gjl.code_combination_id = gcc.code_combination_id
   AND xte.entity_code = 'TRANSACTIONS'
   AND xah.entity_id = xte.entity_id
   AND xah.application_id = xte.application_id
   AND rcta.customer_trx_id = xte.source_id_int_1
   AND xah.application_id = 222
   AND rcta.attribute3 IS NOT NULL
   AND gcc.summary_flag = 'N'
   AND gcc.segment7 = chan_val.flex_value
   AND chan_val.flex_value_set_id = chan_vs.flex_value_set_id
   AND chan_vs.flex_value_set_name = 'TENCENT_CHANNEL'
   AND (gcc.segment3 LIKE '1131%' --应收帐款
       OR gcc.segment3 LIKE '2162%' --递延收益
       OR gcc.segment3 LIKE '5101%' --主营业务收入
       OR gcc.segment3 LIKE '5301%' --营业外收入
       OR gcc.segment3 LIKE '5102%' --其他业务收入
       OR gcc.segment3 IN ('21210600', '21710110', '21710113', '21710114', '21710115'))
   AND gcc.chart_of_accounts_id = gl.chart_of_accounts_id
   AND gjh.ledger_id = gl.ledger_id
   AND rcta.batch_source_id = bs.batch_source_id
   AND rcta.org_id = bs.org_id
   AND cao.org_id = rcta.org_id
   AND gjh.status = 'P' --已过账
      --AND (l_org_id IS NULL OR cao.org_id = l_org_id) --102 参数
   AND cao.org_id = 102 --102 参数
      --AND gjh.period_name = p_period_name --'2007-07' 参数
   AND gjh.period_name = '2014-10' --'2007-07' 参数
   AND gl.ledger_id = 1001 --1001 参数
      --AND (p_currency_code IS NULL OR gjh.currency_code = p_currency_code) --参数币种，如CYN
   AND gjh.currency_code = 'CNY' --参数币种，如CYN
      --AND (p_account_name IS NULL OR gcc.segment3 = p_account_name) --参数科目
   AND sign(instr(bs.name, 'IMS系统')) = '1' --来自IMS
 GROUP BY bs.name,
          gl.ledger_id,
          gl.name,
          gjh.period_name,
          gcc.segment1,
          gcc.segment3,
          gcc.segment7,
          chan_val.description,
          gjh.currency_code;