通过提交请求的方式导入AP发票

DECLARE
  CURSOR cur_err(t_invoice_id NUMBER) IS
    SELECT nvl(flv.meaning, err_tbl.reject_lookup_code) err_msg
      FROM (SELECT air.reject_lookup_code
              FROM ap_interface_rejections air
             WHERE air.parent_id = t_invoice_id
               AND air.parent_table = 'AP_INVOICES_INTERFACE'
            UNION
            SELECT air.reject_lookup_code
              FROM ap_invoice_lines_interface ali, ap_interface_rejections air
             WHERE air.parent_id = ali.invoice_line_id
                  --
               AND ali.invoice_id = t_invoice_id
                  --
               AND air.parent_table = 'AP_INVOICE_LINES_INTERFACE') err_tbl,
           fnd_lookup_values flv
     WHERE flv.lookup_code(+) = err_tbl.reject_lookup_code
          --
       AND flv.language(+) = userenv('lang')
       AND flv.lookup_type(+) = 'REJECT CODE';

  g_user_id  CONSTANT NUMBER := fnd_global.user_id;
  g_login_id CONSTANT NUMBER := fnd_global.login_id;

  l_iface_rec       ap_invoices_interface%ROWTYPE;
  l_iface_lines_rec ap_invoice_lines_interface%ROWTYPE;

  l_group_id   NUMBER;
  l_batch_name VARCHAR2(50) := 'TXXTEST080902';
  l_org_id     NUMBER := 82;
  --
  l_request_id NUMBER;
  l_bl_result  BOOLEAN;
  x_phase      VARCHAR2(100);
  x_status     VARCHAR2(100);
  x_dev_phase  VARCHAR2(100);
  x_dev_status VARCHAR2(100);
  x_message    VARCHAR2(100);
  x_return_msg  VARCHAR2(1000);
  l_cnt        NUMBER := 0;
  --
  l_result               BOOLEAN;
  l_batch_error_flag     VARCHAR2(30);
  l_invoices_fetched     NUMBER;
  l_invoices_created     NUMBER;
  l_total_invoice_amount NUMBER;
  l_print_batch          VARCHAR2(30);
  l_error_count          NUMBER;
BEGIN
  SELECT ap_interface_groups_s.nextval INTO l_group_id FROM dual;

  l_iface_rec                   := NULL; ---清空数据
  l_iface_rec.last_update_date  := SYSDATE;
  l_iface_rec.last_updated_by   := g_user_id;
  l_iface_rec.creation_date     := SYSDATE;
  l_iface_rec.created_by        := g_user_id;
  l_iface_rec.last_update_login := g_login_id;

  l_iface_rec.invoice_num                   := 'HYF'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'); -- 发票号
  l_iface_rec.invoice_type_lookup_code      := 'STANDARD'; -- 发票类型 见快码'INVOICE TYPE'
  l_iface_rec.invoice_date                  := trunc(SYSDATE); -- 发票日期
  l_iface_rec.gl_date                       := trunc(SYSDATE); -- GL日期
  l_iface_rec.vendor_id                     := 1; -- 供应商ID
  l_iface_rec.vendor_site_id                := 1; -- 供应商地点ID
  l_iface_rec.invoice_amount                := 200; -- 发票额
  l_iface_rec.invoice_currency_code         := 'CNY'; -- 币种
  l_iface_rec.exchange_rate_type            := NULL; -- 汇率类型
  l_iface_rec.exchange_date                 := NULL; -- 汇率日期
  l_iface_rec.exchange_rate                 := NULL; -- 汇率
  l_iface_rec.source                        := 'IN'; -- 见快码'SOURCE'需要自定义
  l_iface_rec.group_id                      := l_group_id; -- 组
  l_iface_rec.org_id                        := l_org_id; -- 业务实体
  l_iface_rec.accts_pay_code_combination_id := 1015; -- 负债帐户
  l_iface_rec.description                   := NULL; -- 说明
  l_iface_rec.payment_method_lookup_code    := 'CHECK'; --'支票' 付款方法
  l_iface_rec.terms_id                      := 10000; --'立即付款' 付款条件
  l_iface_rec.terms_date                    := trunc(SYSDATE); -- 条件日期
--  l_iface_rec.attribute1                    := 'TXX';

  SELECT ap_invoices_interface_s.nextval INTO l_iface_rec.invoice_id FROM dual;

  ---insert data
  INSERT INTO ap_invoices_interface VALUES l_iface_rec;

  l_iface_lines_rec                   := NULL;
  l_iface_lines_rec.last_update_date  := SYSDATE;
  l_iface_lines_rec.last_updated_by   := g_user_id;
  l_iface_lines_rec.creation_date     := SYSDATE;
  l_iface_lines_rec.created_by        := g_user_id;
  l_iface_lines_rec.last_update_login := g_login_id;

  l_iface_lines_rec.org_id                   := l_org_id; -- 业务实体
  l_iface_lines_rec.invoice_id               := l_iface_rec.invoice_id;
  l_iface_lines_rec.accounting_date          := trunc(SYSDATE); -- GL日期
  l_iface_lines_rec.line_number              := 1; -- 行号
  l_iface_lines_rec.line_type_lookup_code    := 'ITEM'; -- 行类型
  l_iface_lines_rec.amount                   := l_iface_rec.invoice_amount;
  l_iface_lines_rec.description              := 'TEST';
  l_iface_lines_rec.dist_code_combination_id := 1016; -- 负债帐户

  SELECT ap_invoice_lines_interface_s.nextval INTO l_iface_lines_rec.invoice_line_id FROM dual;

  INSERT INTO ap_invoice_lines_interface VALUES l_iface_lines_rec;

  -- 方式一 提交请求

  l_request_id := fnd_request.submit_request('SQLAP',
                                             'APXIIMPT',
                                             '',
                                             '',
                                             FALSE,
                                             l_org_id,--L_ORG_ID 
                                             'IN', -- 来源
                                             l_group_id, -- Group
                                             l_batch_name, -- 批名
                                             '', -- 暂挂名
                                             '', -- 暂挂原因
                                             TO_CHAR(sysdate,'YYYY-MM-DD'), -- GL日期
                                             'Y', -- 清除
                                             'N', -- 跟踪开关
                                             'N', -- 调试开关
                                             'Y', -- 汇总报告以审核报表
                                             1000, -- 提交批大小
                                             g_user_id, -- 用户标识
                                             g_login_id, -- 登录标识
                                             chr(0));
  IF l_request_id <= 0 THEN
    x_return_msg := '提交标准请求APXIIMPT失败,请联系系统管理员。';
    RAISE fnd_api.g_exc_error;
  END IF;
  COMMIT;
  --
  l_bl_result := fnd_concurrent.wait_for_request(l_request_id,
                                                 1,
                                                 0,
                                                 x_phase,
                                                 x_status,
                                                 x_dev_phase,
                                                 x_dev_status,
                                                 x_message);

  IF upper(x_status) NOT IN ('NORMAL', '正常') THEN
    dbms_output.put_line('请求运行结果异常#' || x_message);
    RETURN;
  ELSE  
    FOR rec_err IN cur_err(l_iface_rec.invoice_id) LOOP
      dbms_output.put_line(rec_err.err_msg);
    END LOOP;
  END IF;

  
  -- 方法二 API处理
/*  l_result := ap_import_invoices_pkg.import_invoices(p_batch_name           => l_batch_name,
                                                     p_gl_date              => NULL,
                                                     p_hold_code            => NULL,
                                                     p_hold_reason          => NULL,
                                                     p_commit_cycles        => NULL, -- 循环处理发票数
                                                     p_source               => 'IN', -- 不可为空
                                                     p_group_id             => l_group_id,
                                                     p_conc_request_id      => fnd_global.conc_request_id,
                                                     p_debug_switch         => 'Y',
                                                     p_batch_error_flag     => l_batch_error_flag, --OUT
                                                     p_invoices_fetched     => l_invoices_fetched, --OUT
                                                     p_invoices_created     => l_invoices_created, --OUT
                                                     p_total_invoice_amount => l_total_invoice_amount, --OUT  for bug 989221
                                                     p_print_batch          => l_print_batch,
                                                     p_calling_sequence     => 'CUX_API');

  IF l_result THEN
    COMMIT;
    dbms_output.put_line('SUCCESS ' || l_invoices_created);
    --API处理成功不代表我们的发票数据成功导入到标准表中,还需要我们去错误表中检验是否有错误数据
    --如果没有,则说明数据全部成功导入,如果有,则可以查找具体是哪些数据出错,还有具体的错误消息
  
    --如果可以查找到 reject_lookup_code 则说明有错误消息,然后关联快码表查找错误消息
    --注  消息分头行消息  
    FOR rec_err IN cur_err(l_iface_rec.invoice_id) LOOP
      dbms_output.put_line(rec_err.err_msg);
    END LOOP;
  ELSE
    dbms_output.put_line('ERROR ' || l_batch_error_flag);
  END IF;*/
END;

发布了11 篇原创文章 · 获赞 1 · 访问量 917
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览