EBS R12 应付发票混合发票导入

EBS R12 应付发票混合发票导入

1.将发票当作贷项通知单导入
2.更新发票类型为混合

  PROCEDURE create_invoices( x_return_status OUT NOCOPY VARCHAR2
                           ,x_msg_count     OUT NOCOPY NUMBER
                           ,x_msg_data      OUT NOCOPY VARCHAR2
                           ,p_attribute10   IN VARCHAR2
                           ,x_ap_invoice_id OUT NUMBER) IS
    l_api_name       CONSTANT VARCHAR2(30) := 'create_invoices';
    l_savepoint_name CONSTANT VARCHAR2(30) := 'create_invoices_sp';
    l_iface_rec          ap_invoices_interface%ROWTYPE;
    l_iface_lines_rec    ap_invoice_lines_interface%ROWTYPE;
    lx_holds_count       NUMBER;
    lx_approval_status   VARCHAR2(240);
    lv_funds_return_code VARCHAR2(240);
    l_success            BOOLEAN;
    --
    v_batch_error_flag     VARCHAR2(30);
    v_invoices_fetched     NUMBER;
    v_invoices_created     NUMBER;
    v_total_invoice_amount NUMBER; -- bug 989221
    v_print_batch_flag     VARCHAR2(30);
    l_input_batch_number   VARCHAR2(80);
    l_amount_sum           NUMBER := 0;
    l_num                  NUMBER;
    l_flag                 VARCHAR2(1);
    l_inv_flag             VARCHAR2(1);
    --  l_count                    NUMBER;
    --  l_sum_amount               NUMBER := 0;
    --    l_diff_dist_account        VARCHAR2(80);
    l_invoice_type_lookup_code VARCHAR2(30); 
    CURSOR cur_l_data(t_attribute10 IN VARCHAR2) IS
    
      SELECT l.iface_id
            ,l.line_number
            ,l.line_type_lookup_code
            ,decode(l.po_match_id
                   ,NULL
                   ,l.line_amount
                   ,l.quantity_invoiced * l.unit_price) line_amount
             
            ,l.tax_amount
            ,l.po_match_id
            ,l.quantity_invoiced
            ,l.unit_price
            ,l.line_dis_account_id
            ,l.attribute21
            ,l.attribute22
            ,l.attribute23
            ,l.attribute24
            ,l.attribute25
            ,l.attribute26
            ,l.attribute27
            ,l.attribute28
            ,l.attribute29
            ,l.attribute30
            ,l.source_header_id
        FROM cux_imp_ap_invoice_iface l
       WHERE 1 = 1
         AND l.request_id = g_request_id
         AND l.imp_status = 'R'
         AND l.attribute10 = t_attribute10
       ORDER BY l.line_amount DESC;
  
    CURSOR cur_po_data(t_iface_id    IN NUMBER
                      ,t_po_match_id IN VARCHAR2) IS
      SELECT tmp.iface_id
            ,tmp.consigned_flag
            ,tmp.po_header_id
            ,tmp.po_line_id
            ,tmp.line_location_id
            ,tmp.po_release_id
            ,tmp.quantity
            ,tmp.transaction_id
            ,tmp.item_id
            ,tmp.unit_of_measure
            ,tmp.mark_id
        FROM cux.cux_ap_po_order_tmp tmp
       WHERE 1 = 1
         AND tmp.iface_id = t_iface_id
         AND tmp.po_match_id = t_po_match_id
       ORDER BY tmp.quantity DESC;
  
    -- l_max_line_num              NUMBER;
    l_old_num                 NUMBER;
    l_org_id                  NUMBER;
    l_org_name                VARCHAR2(240);
    l_vendor_id               NUMBER;
    l_vendor_site_id          NUMBER;
    l_terms_date              DATE;
    l_exchange_date           DATE;
    l_exchange_rate           NUMBER;
    l_terms_id                NUMBER;
    l_payment_method_code     VARCHAR2(80);
    l_invoice_date            DATE;
    l_invoice_num             VARCHAR2(240);
    l_invoice_currency_code   VARCHAR2(80);
    l_invoice_amount          NUMBER;
    l_tax_amount              NUMBER;
    l_diif_tax_amount         NUMBER;
    l_description             VARCHAR2(240);
    l_gl_date                 DATE;
    l_liability_account_id    NUMBER;
    l_tax_classification_code VARCHAR2(80);
    l_source_code             VARCHAR2(80);
    l_attribute1              VARCHAR2(150);
    l_attribute2              VARCHAR2(150);
    l_attribute3              VARCHAR2(150);
    l_attribute4              VARCHAR2(150);
    l_attribute5              VARCHAR2(150);
    l_attribute6              VARCHAR2(150);
    l_attribute7              VARCHAR2(150);
    l_attribute8              VARCHAR2(150);
    l_attribute9              VARCHAR2(150);
    l_attribute10             VARCHAR2(150);
    l_apply_term              VARCHAR2(150);
  
  BEGIN
    x_return_status := cux_api.start_activity(p_pkg_name       => g_pkg_name
                                             ,p_api_name       => l_api_name
                                             ,p_savepoint_name => l_savepoint_name
                                             ,p_init_msg_list  => p_init_msg_list);
    raise_exception(x_return_status);
  
    SELECT org_id
          ,org_name
          ,invoice_type_lookup_code
          ,vendor_id
          ,vendor_site_id
          ,terms_date
          ,exchange_date
          ,exchange_rate
          ,terms_id
          ,payment_method_code
          ,invoice_date
          ,invoice_num
          ,invoice_currency_code
          ,SUM(invoice_amount) invoice_amount
          ,SUM(tax_amount) tax_amount
          ,SUM(diif_tax_amount) diif_tax_amount
          ,description
          ,gl_date
          ,liability_account_id
          ,tax_classification_code
          ,source_code
          ,attribute1
          ,attribute2
          ,attribute3
          ,attribute4
          ,attribute5
          ,attribute6
          ,attribute7
          ,attribute8
          ,attribute9
          ,attribute10
          ,apply_term
      INTO l_org_id
          ,l_org_name
          ,l_invoice_type_lookup_code
          ,l_vendor_id
          ,l_vendor_site_id
          ,l_terms_date
          ,l_exchange_date
          ,l_exchange_rate
          ,l_terms_id
          ,l_payment_method_code
          ,l_invoice_date
          ,l_invoice_num
          ,l_invoice_currency_code
          ,l_invoice_amount