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_je_category_name);      SELECT gl.gl_interface_control_s.nextval INTO g_group_id FROM dual;      FOR rec_data IN get_data    LOOP          l_gl_interface_rec := l_clear;          -- 日历      BEGIN        SELECT gp.period_name          INTO l_period_name          FROM gl_periods_v gp         WHERE gp.period_set_name = 'ZJG_CALENDAR'           AND gp.adjustment_period_flag = 'N'           AND rec_data.accounting_date BETWEEN gp.start_date AND               gp.end_date;      EXCEPTION        WHEN OTHERS THEN          l_period_name := NULL;      END;          -- 转入银行   中转科目      BEGIN        SELECT cba.asset_code_combination_id          INTO l_ccid_to          FROM ce_bank_accounts cba         WHERE cba.bank_account_id =               get_transfer_account(rec_data.org_id,                                    rec_data.transfer_in_currency_code,                                    rec_data.transfer_out_currency_code);      EXCEPTION        WHEN OTHERS THEN          l_ccid_to := NULL;      END;          -- 转出银行      BEGIN        SELECT cba.asset_code_combination_id          INTO l_ccid_fm          FROM ce_bank_accounts cba         WHERE cba.bank_account_name = rec_data.transfer_out_bank_account;      EXCEPTION        WHEN OTHERS THEN          l_ccid_fm := NULL;      END;          BEGIN        SELECT gl.ledger_id          INTO l_ledger_id          FROM gl_ledgers gl, gl_sets_of_books sob         WHERE gl.name = sob.name           AND sob.set_of_books_id = g_sob_id;      EXCEPTION        WHEN OTHERS THEN          l_ledger_id := NULL;      END;      cux_conc_utl.log_msg('l_ledger_id= ' || l_ledger_id);      --1. debit      l_gl_interface_rec.status          := 'NEW';      l_gl_interface_rec.set_of_books_id := g_sob_id;      l_gl_interface_rec.ledger_id       := l_ledger_id;      l_gl_interface_rec.date_created    := l_sysdate;      l_gl_interface_rec.created_by      := l_user_id;      l_gl_interface_rec.actual_flag     := 'A';      l_gl_interface_rec.reference1      := l_batch_name; -- 凭证批名      l_gl_interface_rec.reference2      := l_je_desc; -- 批说明      l_gl_interface_rec.reference4      := l_je_name; -- 日记帐名称       l_gl_interface_rec.reference10     := rec_data.forex_bill_num ||                                            rec_data.memo; -- 日记帐行摘要          l_gl_interface_rec.user_je_source_name   := l_je_source_name;      l_gl_interface_rec.user_je_category_name := l_je_category_name;      l_gl_interface_rec.accounting_date       := rec_data.accounting_date;      l_gl_interface_rec.period_name           := l_period_name;      l_gl_interface_rec.currency_code         := rec_data.transfer_out_currency_code;          l_gl_interface_rec.code_combination_id := l_ccid_to; --借方 : 取转入银行账户的现金账户      l_gl_interface_rec.entered_dr          := rec_data.transfer_out_amount;      l_gl_interface_rec.entered_cr          := NULL;      l_gl_interface_rec.accounted_dr        := rec_data.transfer_out_rate_amount;      l_gl_interface_rec.accounted_cr        := NULL;          l_gl_interface_rec.group_id                      := g_group_id;      l_gl_interface_rec.user_currency_conversion_type := rec_data.transfer_out_type_code;      l_gl_interface_rec.currency_conversion_date      := rec_data.accounting_date;      l_gl_interface_rec.currency_conversion_rate      := rec_data.transfer_out_rate;          -- 现金流量标识      l_gl_interface_rec.context3    := get_segment(l_ccid_to, 2);      l_gl_interface_rec.attribute10 := rec_data.forex_bill_id; --购汇单ID      l_gl_interface_rec.attribute11 := 500;          -- 插入接口表      INSERT INTO gl_interface VALUES l_gl_interface_rec;          --2. credit      l_gl_interface_rec.code_combination_id := l_ccid_fm; --贷方 : 取转出银行账户的现金账户      l_gl_interface_rec.entered_dr          := NULL;      l_gl_interface_rec.entered_cr          := rec_data.transfer_out_amount;      l_gl_interface_rec.accounted_dr        := NULL;      l_gl_interface_rec.accounted_cr        := rec_data.transfer_out_rate_amount;          l_gl_interface_rec.context3 := get_segment(l_ccid_fm, 2);          -- 插入接口表      INSERT INTO gl_interface VALUES l_gl_interface_rec;          l_count := l_count + 1;          --插入第二张凭证          BEGIN        SELECT cba.asset_code_combination_id          INTO l_ccid_fm          FROM ce_bank_accounts cba         WHERE cba.bank_account_id =               get_transfer_account(rec_data.org_id,                                    rec_data.transfer_in_currency_code,                                    rec_data.transfer_out_currency_code);      EXCEPTION        WHEN OTHERS THEN          l_ccid_fm := NULL;      END;          BEGIN        SELECT cba.asset_code_combination_id          INTO l_ccid_to          FROM ce_bank_accounts cba         WHERE cba.bank_account_name = rec_data.transfer_in_bank_account;      EXCEPTION        WHEN OTHERS THEN          l_ccid_to := NULL;      END;          --1. debit           l_gl_interface_rec.currency_code            := rec_data.transfer_in_currency_code;      l_gl_interface_rec.currency_conversion_rate := rec_data.transfer_in_rate;          l_gl_interface_rec.code_combination_id := l_ccid_to; --借方 : 取转入银行账户的现金账户      l_gl_interface_rec.entered_dr          := rec_data.transfer_in_amount;      l_gl_interface_rec.entered_cr          := NULL;      l_gl_interface_rec.accounted_dr        := rec_data.transfer_in_rate_amount;      l_gl_interface_rec.accounted_cr        := NULL;          l_gl_interface_rec.user_currency_conversion_type := rec_data.transfer_in_type_code;      l_gl_interface_rec.context3                      := get_segment(l_ccid_to,                                                                      2);      INSERT INTO gl_interface VALUES l_gl_interface_rec;          --2. credit      l_gl_interface_rec.code_combination_id := l_ccid_fm; --贷方 : 取转出银行账户的现金账户      l_gl_interface_rec.entered_dr          := NULL;      l_gl_interface_rec.entered_cr          := rec_data.transfer_in_amount;      l_gl_interface_rec.accounted_dr        := NULL;      l_gl_interface_rec.accounted_cr        := rec_data.transfer_in_rate_amount;          l_gl_interface_rec.context3 := get_segment(l_ccid_fm, 2);      INSERT INTO gl_interface VALUES l_gl_interface_rec;      l_count := l_count + 1;        END LOOP; -- FOR rec_data IN get_data LOOP      cux_agm_public_pkg.output('已成功进入接口表记录数:' || l_count);    EXCEPTION    WHEN OTHERS THEN      cux_agm_public_pkg.output('import_data错误结束:' || SQLERRM);      x_return_status := 'E';  END import_data;

2.运行导入总账请求和客户化过账请求

PROCEDURE submmit_request(p_batch_id      IN NUMBER,                          x_return_status OUT NOCOPY VARCHAR2) IS  l_wait_req     BOOLEAN;  l_child_phase  VARCHAR2(100);  l_child_status VARCHAR2(100);  l_dev_phase    VARCHAR2(100);  l_dev_status   VARCHAR2(100);  l_message      VARCHAR2(100);  l_error EXCEPTION;  v_req_id     NUMBER;  l_request_id NUMBER;  l_import_sec NUMBER;  l_batch_id   NUMBER;  l_batch_name VARCHAR2(500);BEGIN  l_request_id := fnd_global.conc_request_id;  l_import_sec := gl_interface_control_pkg.get_unique_run_id;  gl_interface_control_pkg.insert_row(xinterface_run_id => l_import_sec,                                      xje_source_name   => g_je_source_name,                                      xledger_id        => g_sob_id,                                      xgroup_id         => g_group_id,                                      xpacket_id        => NULL);  cux_agm_public_pkg.output('submmit_request开始运行' || l_request_id);  --提交标准请求  v_req_id := fnd_request.submit_request('SQLGL',                                         'GLLEZL',                                         '',                                         to_char(SYSDATE,                                                 'YYYY/MM/DD HH24:MI:SS'),                                         FALSE,                                         to_char(l_import_sec), -- Argument1                                         to_char(g_sob_id), --帐簿                                         'N',                                         NULL,                                         NULL,                                         'N',                                         'O', --是否导入弹性域字段,三种方式 N(不导入),W(验证导入WITH VALIDATE),O(不验证WITHOUT VALIDATE)                                         chr(0));  IF v_req_id <= 0 OR v_req_id IS NULL THEN    cux_agm_public_pkg.output('提交 程序 - 导入日记帐 时发生错误');    RAISE l_error;  ELSE    COMMIT;    cux_agm_public_pkg.output('已成功提交请求，request_id=' || v_req_id);  END IF;  --等待请求完成    cux_agm_public_pkg.output('等待请求' || l_request_id);  l_wait_req := fnd_concurrent.wait_for_request(v_req_id,                                                1,                                                0,                                                l_child_phase,                                                l_child_status,                                                l_dev_phase,                                                l_dev_status,                                                l_message);  IF l_child_status <> '正常' THEN    cux_agm_public_pkg.output('并发请求' || v_req_id || '错误结束：' || l_message);    x_return_status := 'E';    --这里不抛出例外是因为需要进行正常的错误处理，错了就错了  ELSE    x_return_status := 'S';    cux_agm_public_pkg.output('并发请求' || v_req_id || '正常结束！');      -- 自动过账    BEGIN      BEGIN        SELECT gb.je_batch_id, gb.name          INTO l_batch_id, l_batch_name          FROM gl_je_batches gb         WHERE gb.name LIKE l_batch_name || '%'           AND gb.group_id = g_group_id           AND rownum = 1;      EXCEPTION        WHEN OTHERS THEN          l_batch_id   := NULL;          l_batch_name := NULL;      END;      g_batch_id := l_batch_id;      --IF l_debug = 'Y' THEN --LOG MESSAGE      cux_conc_utl.out_msg('system gl batch name：' || l_batch_name);      --END IF;            l_request_id := fnd_request.submit_request(application => 'CUX',                                                 program     => 'CUXPOSTBH',                                                 description => NULL,                                                 start_time  => NULL,                                                 sub_request => FALSE,                                                 argument1   => l_batch_name);      IF (l_request_id <= 0 OR l_request_id IS NULL) THEN        fnd_message.set_name('SQLGL', 'GL_API_CONC_REQ_SUBMIT_FAIL');        fnd_msg_pub.add;        x_return_status := fnd_api.g_ret_sts_error;        RAISE fnd_api.g_exc_error;      ELSE        update_je_header(p_batch_id);        COMMIT;      END IF;    EXCEPTION      WHEN OTHERS THEN        cux_conc_utl.out_msg('凭证没有自动过帐，请检查更正后重新提交。');        RAISE fnd_api.g_exc_error;    END;  END IF;  cux_agm_public_pkg.output('submmit_request正常结束');EXCEPTION  WHEN OTHERS THEN    cux_agm_public_pkg.output('submmit_request错误结束:' || SQLERRM);    x_return_status := 'E';END submmit_request;

其中自动过账请求CUXPOSTBH程序包如下：

CREATE OR REPLACE PACKAGE BODY cux_post_batch_pkg IS  g_pkg_name VARCHAR2(100) := 'cux_post_batch_pkg';  PROCEDURE main(errbuf       OUT VARCHAR2,                 retcode      OUT VARCHAR2,                 p_batch_name VARCHAR2) IS    l_request_id     NUMBER;    l_return_status  VARCHAR2(1) := fnd_api.g_ret_sts_success;    l_msg_count      NUMBER;    l_msg_data       VARCHAR2(2000);    l_ledger_id      NUMBER;    l_posting_run_id NUMBER;    l_access_set_id  NUMBER := fnd_profile.value('GL_ACCESS_SET_ID');    CURSOR cur IS      SELECT a.rowid, a.* FROM gl_je_batches a WHERE a.name = p_batch_name;  BEGIN    retcode := '0';    -- concurrent header log    hand_conc_utl.log_header;    FOR a IN cur LOOP      SELECT gl_je_batches_post_pkg.get_unique_id        INTO l_posting_run_id        FROM dual;      BEGIN        SELECT MAX(jeh.ledger_id)          INTO l_ledger_id          FROM gl_je_headers jeh         WHERE jeh.je_batch_id = a.je_batch_id         GROUP BY jeh.je_batch_id        HAVING COUNT(DISTINCT jeh.ledger_id) = 1;      EXCEPTION        WHEN OTHERS THEN          l_ledger_id := NULL;      END;      --更新批的状态和posting_run_id      gl_je_batches_post_pkg.update_row(x_rowid                    => a.rowid,                                        x_je_batch_id              => a.je_batch_id,                                        x_last_update_date         => a.last_update_date,                                        x_last_updated_by          => a.last_updated_by,                                        x_chart_of_accounts_id     => a.chart_of_accounts_id,                                        x_name                     => a.name,                                        x_status                   => 'S',                                        x_status_verified          => a.status_verified,                                        x_actual_flag              => a.actual_flag,                                        x_budgetary_control_status => a.budgetary_control_status,                                        x_last_update_login        => a.last_update_login,                                        x_default_period_name      => a.default_period_name,                                        x_control_total            => a.control_total,                                        x_running_total_dr         => a.running_total_dr,                                        x_running_total_cr         => a.running_total_cr,                                        x_posting_run_id           => l_posting_run_id,                                        x_request_id               => a.request_id);      --运行请求      l_request_id := fnd_request.submit_request(application => 'SQLGL',                                                 program     => 'GLPPOSS',                                                 description => NULL,                                                 start_time  => NULL,                                                 sub_request => FALSE,                                                 argument1   => l_ledger_id,                                                 argument2   => l_access_set_id, --'1000', --默认  --add by tsg for mantis 0004375                                                 argument3   => a.chart_of_accounts_id,                                                 argument4   => l_posting_run_id);      IF l_request_id > 0 THEN        COMMIT;      END IF;    END LOOP;    IF (l_return_status = fnd_api.g_ret_sts_error) THEN      RAISE fnd_api.g_exc_error;    ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN      RAISE fnd_api.g_exc_unexpected_error;    END IF;    -- conc end body    -- concurrent footer log    hand_conc_utl.log_footer;  EXCEPTION    WHEN fnd_api.g_exc_error THEN      hand_conc_utl.log_message_list;      retcode := '1';      fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,                                p_count   => l_msg_count,                                p_data    => l_msg_data);      IF l_msg_count > 1 THEN        l_msg_data := fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first,                                             p_encoded   => fnd_api.g_false);      END IF;      errbuf := l_msg_data;    WHEN fnd_api.g_exc_unexpected_error THEN      hand_conc_utl.log_message_list;      retcode := '2';      fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,                                p_count   => l_msg_count,                                p_data    => l_msg_data);      IF l_msg_count > 1 THEN        l_msg_data := fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first,                                             p_encoded   => fnd_api.g_false);      END IF;      errbuf := l_msg_data;    WHEN OTHERS THEN      fnd_msg_pub.add_exc_msg(p_pkg_name       => g_pkg_name,                              p_procedure_name => 'MAIN',                              p_error_text     => substrb(SQLERRM, 1, 240));      hand_conc_utl.log_message_list;      retcode := '2';      errbuf  := SQLERRM;  END;  function get_gl_doc(p_group_id number, p_batch_name varchar2) return number is  l_doc_seq number;begin  select h.doc_sequence_value    into l_doc_seq    from gl_je_batches b, gl_je_headers h   WHERE h.je_batch_id + 0 = b.je_batch_id + 0     AND (h.display_alc_journal_flag = 'Y' or         h.display_alc_journal_flag is null)     and b.group_id = p_group_id     and b.name like p_batch_name || '%'     and h.doc_sequence_value is not null     and rownum = 1;  return l_doc_seq;exception  when others then    return null;end;function get_extra_fee(p_DOC_BILLS_ID number,p_Extra_Fee number ) return number is  l_sum number;begin   select nvl(sum(CDOC.Extra_Fee),0)     into l_sum     from CUX_DOC_OUT_CST CDOC    where  cdoc.extra_impflag = 'Y'     and cdoc.doc_bills_id =  p_DOC_BILLS_ID ;     if l_sum =0 and p_Extra_Fee is   null then       return null;     else       return (l_sum+nvl(p_Extra_Fee,0));    end if;exception  when others then    return null;end;END cux_post_batch_pkg;

至此，可以查看总账模块。看是否总账和分录是否生成。