从EBS导出Journal Entry

/*从EBS导出Journal Entry

用于从Oracle EBS 11i(数据库为Oracle 9i)中取出某一段时间的所有Journal Entry。要select出来的信息包括：

1、公司名称/代码
2、会计科目名称
3、记账凭证号码（
4、记账日期
5、记账币种（最好有原币和本位币，至少有本位币）
6、记账金额（最好有原币金额和本位币金额，至少有本位币金额）
7、记账摘要/描述*/

--科目名称那个要做个函数,参考下没科目名称的
SELECT gjh.NAME 日记帐名称,
       gjh.period_name 期间,
       gjh.doc_sequence_value 单据编号,
       gjh.creation_date 创建日期,
       gjl.entered_dr 借项,
       gjl.entered_cr 贷项,
       gjl.accounted_dr 本位币借项,
       gjl.accounted_cr 本位币贷项,
       gcc.segment3 科目,
       gjh.currency_code,
       fu.user_name || '--' || fu.description
  FROM gl_je_headers        gjh,
       gl_je_lines          gjl,
       gl_code_combinations gcc,
       fnd_user             fu
 WHERE gjh.je_header_id = gjl.je_header_id
   AND gjh.set_of_books_id = gjl.set_of_books_id
   AND gcc.code_combination_id = gjl.code_combination_id
   AND gjh.set_of_books_id = 3
   AND fu.user_id = gjh.created_by
      --AND GJH.Period_Name='01-2008'
      --ND gjh.period_name <='04-2008'
   AND gjh.currency_code <> 'RMB'
 ORDER BY 2,
          4
         
注意：R12 中总账头和行表中不存在set_of_books_id而是用ledger_id替代。

--Journal Entry File:

SELECT REPLACE(REPLACE(REPLACE(gjh.NAME,
                               chr(13),
                               chr(32)),
                       chr(10),
                       chr(32)),
               ',',
               '_') journal_number,
       gcc.concatenated_segments gl_account_number,
       nvl(gjl.accounted_dr,
           0) - nvl(gjl.accounted_cr,
                    0) amount,
       gcc.segment1 business_unit,
       gjl.effective_date effective_date,
       gjl.period_name period_name,
       gjl.creation_date entry_date,
       gjl.created_by preparer_id,
       gjh.je_source SOURCE,
       REPLACE(REPLACE(REPLACE(gjl.description,
                               chr(13),
                               chr(32)),
                       chr(10),
                       chr(32)),
               ',',
               '_') description,
       gl.description account_type,
       gcc.segment2 department,
       gjh.posted_date posted_date,
       gjl.code_combination_id account_id
  FROM gl_je_headers            gjh,
       gl_je_lines              gjl,
       gl_code_combinations_kfv gcc,
       gl_lookups               gl
 WHERE gjh.je_header_id = gjl.je_header_id
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gcc.gl_account_type = gl.lookup_code
   AND gl.lookup_type = 'ACCOUNT TYPE'
   AND gjl.status = 'P'
   AND trunc(gjl.effective_date) BETWEEN '1-JAN-2008' AND '31-DEC-2008'
 ORDER BY gcc.concatenated_segments

 

--GL Balance File:
SELECT account,
       account_type,
       SUM(beginning_balance) beginning_balance,
       SUM(ending_balance) ending_balance
  FROM (SELECT gcc.concatenated_segments account,
               gl.description account_type,
               nvl(gb1.begin_balance_dr,
                   0) - nvl(gb1.begin_balance_cr,
                            0) beginning_balance,
               0 ending_balance
          FROM gl_balances              gb1,
               gl_periods               gp1,
               gl_lookups               gl,
               gl_code_combinations_kfv gcc
         WHERE gp1.period_name = gb1.period_name
           AND gp1.period_type = gb1.period_type
           AND gp1.period_year = gb1.period_year
           AND gp1.period_num = gb1.period_num
           AND gb1.actual_flag = 'A'
           AND gb1.code_combination_id = gcc.code_combination_id
           AND gcc.gl_account_type = gl.lookup_code
           AND gl.lookup_type = 'ACCOUNT TYPE'
           AND gp1.start_date = to_date('2010-10-01',
                                        'YYYY-MM-DD')
        UNION
        SELECT gcc.concatenated_segments account,
               gl.description account_type,
               0 beginning_balance,
               nvl(gb1.begin_balance_dr,
                   0) - nvl(gb1.begin_balance_cr,
                            0) + nvl(period_net_dr,
                                     0) - nvl(period_net_cr,
                                              0) ending_balance
          FROM gl_balances              gb1,
               gl_periods               gp1,
               gl_lookups               gl,
               gl_code_combinations_kfv gcc
         WHERE gp1.period_name = gb1.period_name
           AND gp1.period_type = gb1.period_type
           AND gp1.period_year = gb1.period_year
           AND gp1.period_num = gb1.period_num
           AND gb1.actual_flag = 'A'
           AND gb1.code_combination_id = gcc.code_combination_id
           AND gcc.gl_account_type = gl.lookup_code
           AND gl.lookup_type = 'ACCOUNT TYPE'
           AND gp1.end_date = to_date('2010-10-01',
                                        'YYYY-MM-DD'))
 GROUP BY account,
          account_type

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/126211/viewspace-687684/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/126211/viewspace-687684/