GL接口导入

这周学习了一下GL导入总账方面的知识，今天下面做一个简短的总结。

GL导入主要是用来生成日记账及其分录。

1.导入接口表

GL总账接口表为  gl.gl_interface  关于其各个字段意义可以参考 《gl_interface的详细说明》。

需要补充说明：

gl_interface的attribute1到attribute10 对应gl_je_lines表的attribute1到attribute10字段

              attribute11到attribute20 对应GL_CAPTURED_INFO表的attribute11到attribute20字段

在启用接口表的时候需要注意。

PROCEDURE import_data(p_batch_id      IN NUMBER,
                        x_return_status OUT NOCOPY VARCHAR2) IS
  
    --获取需要导入的数据
    CURSOR get_data IS
      SELECT *
        FROM cux_forex_transfer_bills_v ftb
       WHERE ftb.attribute1 = p_batch_id -- 本次要导入的批
       ORDER BY ftb.forex_bill_num;
  
    l_request_id       NUMBER;
    l_conc_program_id  NUMBER;
    l_prog_appl_id     NUMBER;
    l_user_id          NUMBER;
    l_login_id         NUMBER;
    l_count            NUMBER;
    l_sysdate          DATE;
    l_batch_name       VARCHAR2(100);
    l_je_name          VARCHAR2(100);
    l_je_source_name   VARCHAR2(100);
    l_je_category_name VARCHAR2(100);
    l_period_name      VARCHAR2(100);
    l_ccid_to          NUMBER; -- 转入银行账户的现金账户
    l_ccid_fm          NUMBER; -- 转出银行账户的现金账户
    l_ledger_id        NUMBER;
    l_gl_interface_rec gl_interface%ROWTYPE;
    l_clear            gl_interface%ROWTYPE;
    l_bill_num         VARCHAR2(1000);
    l_je_desc          VARCHAR2(1000);
  BEGIN
    -- init
    l_request_id      := fnd_global.conc_request_id;
    l_conc_program_id := fnd_global.conc_program_id;
    l_prog_appl_id    := fnd_global.prog_appl_id;
    l_user_id         := fnd_global.user_id;
    l_login_id        := fnd_global.login_id;
    l_count           := 0;
    l_sysdate         := SYSDATE;
    g_sob_id          := fnd_profile.value('GL_SET_OF_BKS_ID');
  
    BEGIN
      SELECT sob.chart_of_accounts_id
        INTO g_chart_of_accounts_id
        FROM gl_sets_of_books sob
       WHERE sob.set_of_books_id = g_sob_id;
    EXCEPTION
      WHEN OTHERS THEN
        g_chart_of_accounts_id := NULL;
    END;
  
    --批名
    l_batch_name := cux_doc_sequences_utl.next_seq_number(p_doc_type   => 'FOREX_TRANSFER_BILLS_BATCH' ||
                                                                          g_sob_id,
                                                          p_doc_prefix => l_sysdate || '购汇',
                                                          p_seq_length => 3,
                                                          p_pk1_value  => l_sysdate || '购汇');
    --日记账名
    l_je_name := cux_doc_sequences_utl.next_seq_number(p_doc_type   => 'FOREX_TRANSFER_BILLS_JE' ||
                                                                       g_sob_id,
                                                       p_doc_prefix => '',
                                                       p_seq_length => '',
                                                       p_pk1_value  => '');
    cux_conc_utl.log_msg('l_batch_name= ' || l_batch_name);
    cux_conc_utl.log_msg('l_je_name= ' || l_je_name);
  
    g_batch_name := l_batch_name;
    g_je_name    := l_je_name; 
    FOR r IN get_data
    LOOP
    
      SELECT l_bill_num || nvl2(l_bill_num, '&', NULL) || r.forex_bill_num
        INTO l_bill_num
        FROM dual;
    
    END LOOP;
  
    l_je_desc := l_bill_num;
    cux_conc_utl.log_msg('l_je_desc= ' || l_je_desc);
  
    -- 来源  
    BEGIN
      SELECT gs.user_je_source_name
        INTO l_je_source_name
        FROM gl_je_sources_vl gs
       WHERE gs.je_source_key = 'EXCHANGE';  
    EXCEPTION
      WHEN OTHERS THEN
        l_je_source_name := NULL;
    END;
  
    BEGIN
      SELECT gs.je_source_name
        INTO g_je_source_name
        FROM gl_je_sources_vl gs
       WHERE gs.je_source_key = 'EXCHANGE';  
    EXCEPTION
      WHEN OTHERS THEN
        g_je_source_name := NULL;
    END;
  
    -- 类别  
    BEGIN
      SELECT gjc.user_je_category_name
        INTO l_je_category_name
        FROM gl_je_categories_vl gjc
       WHERE gjc.je_category_key = 'EXCHANGE';  
    EXCEPTION
      WHEN OTHERS THEN
        l_je_category_name := NULL;
    END;
    cux_conc_utl.log_msg('l_je_source_name= ' || l_je_source_name);
    cux_conc_utl.log_msg('g_je_source_name= ' || g_je_source_name);
    cux_conc_utl.log_msg('l_je_category_name= ' || l_