EBS开发_创建AP付款

--以下代码于20181002在Tony环境测试通过
/*
BEGIN
  --mo_global.init('SQLAP');
  apps.fnd_global.apps_initialize(user_id => 1110, resp_id => 50597, resp_appl_id => 20003);
  --mo_global.set_policy_context('S', 0);
  mo_global.init('M');--务必加上这个语句
END;

-- ALTER SESSION SET NLS_LANGUAGE = 'SIMPLIFIED CHINESE';
*/

/*select * from ap_checks;

select * from po_headers_all
*/
DECLARE
  g_user_id  CONSTANT NUMBER := fnd_global.user_id;
  g_login_id CONSTANT NUMBER := fnd_global.login_id;
  --
  l_invoice_id             NUMBER := 10103; 
  l_period_name            VARCHAR2(20) := '2018-10';
  l_set_of_books_id        NUMBER;
  l_exchange_rate          NUMBER;
  l_exchange_date          DATE;
  l_when_payment_accounted ap_system_parameters_all.when_to_account_pmt%TYPE;
  l_count                  NUMBER;
  l_stock_name             VARCHAR2(50);
  l_in_use_by              VARCHAR2(20);
  l_doc_category_code      VARCHAR2(30);
  l_last_document_num      NUMBER;
  l_quick_check            VARCHAR2(200);
  l_hold_all_payments_flag VARCHAR2(1);
  l_payment_type_flag      VARCHAR2(1) := 'Q'; --人工M，快速Q，退款R  
  l_status_lookup_code     VARCHAR2(20) := 'NEGOTIABLE';
  --
  l_row_id                     VARCHAR2(240);
  l_invoice_amount             NUMBER; -- 付款额
  l_bank_account_id            NUMBER;
  l_bank_account_name          VARCHAR2(120);
  l_gl_date                    DATE := last_day(trunc(SYSDATE));
  l_check_id                   NUMBER;
  l_check_number               NUMBER;
  l_invoice_currency_code      VARCHAR2(10);
  l_payment_method_lookup_code VARCHAR2(25);
  l_address_line1              VARCHAR2(240);
  l_address_line2              VARCHAR2(240);
  l_address_line3              VARCHAR2(240);
  l_checkrun_name              VARCHAR2(50); -- 批名
  l_check_format_id            NUMBER;
  l_check_stock_id             NUMBER;
  l_city                       VARCHAR2(25);
  l_country                    VARCHAR2(25);
  l_bank_account_num           VARCHAR2(30);
  l_bank_account_type          VARCHAR2(30);
  l_zip                        VARCHAR2(20);
  l_province                   VARCHAR2(150);
  l_state                      VARCHAR2(150);
  l_address_line4              VARCHAR2(240);
  l_county                     VARCHAR2(25);
  l_address_style              VARCHAR2(30);
  l_org_id                     NUMBER;
  l_vendor_id                  NUMBER;
  l_vendor_site_id             NUMBER;
  l_VENDOR_SITE_CODE           VARCHAR2(30);
  l_VENDOR_NAME                VARCHAR2(3000);
  l_exchange_rate_type         VARCHAR2(30);
  l_party_site_id              number;
  l_party_id                   number;
  l_ap_checks_all_row          ap_checks_all%ROWTYPE;
  --
  l_accounting_event_id NUMBER;
  --
  l_invoice_payment_id NUMBER;
  --
  l_msg_count NUMBER;
  l_msg_data  VARCHAR2(1000);

  l_err_msg VARCHAR2(10000);
  PROCEDURE log(p_msg IN VARCHAR2) IS
  BEGIN
    dbms_output.put_line(p_msg);
  END;

  PROCEDURE get_msg(x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2) IS
  BEGIN
    fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                              p_count   => x_msg_count,
                              p_data    => x_msg_data);
    IF x_msg_count > 1 THEN
      x_msg_data := fnd_msg_pub.get_detail(p_msg_index => fnd_msg_pub.g_first,
                                           p_encoded   => fnd_api.g_false);
    END IF;
  END;

BEGIN

  dbms_output.enable(100000);
  -- 1.0 获取发票信息
  log('Step1.0 获取付款发票信息');
  SELECT aia.invoice_amount,
         aia.invoice_currency_code,
         aia.set_of_books_id,
         aia.org_id,
         aia.vendor_id,
         aia.vendor_site_id,
         aia.exchange_rate,
         aia.exchange_rate_type,
         aia.exchange_date,
         (SELECT pv.VENDOR_NAME from po_vendors pv where pv.VENDOR_ID = aia.vendor_id )
    INTO l_invoice_amount,
         l_invoice_currency_code,
         l_set_of_books_id,
         l_org_id,
         l_vendor_id,
         l_vendor_site_id,
         l_exchange_rate,
         l_exchange_rate_type,
         l_exchange_date,
         l_VENDOR_NAME
    FROM ap_invoices_all aia
   WHERE aia.invoice_id = l_invoice_id;

  SELECT t.party_site_id, a.party_id
    into l_party_site_id, l_party_id
  
    FROM ap_supplier_sites_all t, ap_suppliers a
  
   WHERE a.vendor_id = t.vendor_id
        
     AND t.vendor_site_id = l_vendor_site_id; --p_vendor_site_id;   

  -- 1.2 获取GL_DATE
  log('Step1.2 获取检查应付款选项设置参数 BEGIN');
  -- 对应路径 应付超级职责 -> 设置 -> 选项 -> 应付款
  -- 页签  本位币, 付款会计.将付款记账: 发放付款时, 币种.使用多币种
  BEGIN
    SELECT t.when_to_account_pmt
      INTO l_when_payment_accounted
      FROM ap_system_parameters_all t
     WHERE t.set_of_books_id = l_set_of_books_id
       AND t.org_id = l_org_id;
  EXCEPTION
    WHEN OTHERS THEN
      l_err_msg := '获取业务实体信息(' || l_org_id || ')异常！' || SQLCODE || ',' ||
                   SQLERRM;
      log(l_err_msg);
      --RETURN;
  END;
  log('Step1.2 获取检查应付款选项设置参数 END WHEN[' || l_when_payment_accounted || ']');

  -- 1.3 获取银行信息
  log('Step1.3 获取付款银行信息 begin');
  BEGIN
    /*    SELECT aba.bank_account_id, aba.bank_account_num, aba.bank_account_name, aba.bank_account_type
     INTO l_bank_account_id, l_bank_account_num, l_bank_account_name, l_bank_account_type
     FROM ap_bank_accounts_all aba
    WHERE aba.bank_account_name = '虚拟银行'
      AND aba.org_id = l_org_id;*/
  
    SELECT ibybanks.bank_account_id,
           ibybanks.bank_account_number,
           ibybanks.bank_account_name,
           ibybanks.bank_account_type
    /*       ibybanks.bank_name, --银行 
    ibybanks.bank_branch_name, --分行
    ibybanks.bank_account_num_electronic, --银行帐号
    ibybanks.**/
      INTO l_bank_account_id,
           l_bank_account_num,
           l_bank_account_name,
           l_bank_account_type
      FROM iby_external_payers_all iepa,
           iby_pmt_instr_uses_all  ipiua,
           iby_ext_bank_accounts_v ibybanks
     WHERE 1 = 1 --iepa.payment_function = 'CUSTOMER_PAYMENT'
          -- AND iepa.cust_account_id = p_customer_id --客户帐号ID
       AND ibybanks.bank_account_id = ipiua.instrument_id
          -- AND ipiua.instrument_type = 'BANKACCOUNT'
       AND ipiua.ext_pmt_party_id = iepa.EXT_PAYER_ID
          --AND ipiua.payment_function = 'CUSTOMER_PAYMENT'
       AND sysdate BETWEEN ibybanks.start_date AND
           nvl(ibybanks.end_date, sysdate + 1);
  
  EXCEPTION
    WHEN OTHERS THEN
      l_err_msg := '获取付款银行 虚拟银行 异常！' || SQLCODE || ',' || SQLERRM;
      log(l_err_msg);
      RAISE fnd_api.g_exc_error;
  END;

  log('Step1.3.1 验证付款单据');
  /*  IF l_bank_account_id IS NOT NULL THEN
    SELECT COUNT(1)
      INTO l_count
      FROM ap_check_stocks_all acs
     WHERE acs.bank_account_id = l_bank_account_id;
    IF l_count = 0 THEN
      log('虚拟银行有误，找不到付款单据');
      RAISE fnd_api.g_exc_error;
    END IF;
  END IF;*/

  log('Step1.4.1 获取付款单据号');
  /*  BEGIN
    SELECT NAME,
           in_use_by,
           check_stock_id,
           check_format_id,
           payment_method_lookup_code,
           doc_category_code,
           last_available_document_num
      INTO l_stock_name,
           l_in_use_by,
           l_check_stock_id,
           l_check_format_id,
           l_payment_method_lookup_code,
           l_doc_category_code,
           l_last_document_num
      FROM ap_check_stocks_active_v acs
     WHERE (acs.format_currency_code = 'CNY' OR
           (acs.format_currency_code IS NULL AND acs.format_multi_currency_flag = 'Y' AND
           acs.bank_multi_currency_flag = 'Y'))
       AND acs.disbursement_type_lookup_code != 'RECORDED'
       AND SYSDATE < nvl(acs.inactive_date, SYSDATE + 1)
       AND acs.payment_method_lookup_code = 'CHECK'
       AND acs.bank_account_id = l_bank_account_id;
  EXCEPTION
    WHEN OTHERS THEN
      l_err_msg := '获取付款银行帐号异常:' || SQLCODE || ',' || SQLERRM;
      log(l_err_msg);
      --RETURN;
  END;*/
  log('Step1.4.1 获取付款单据[ID: ' || l_check_stock_id || ']');

  IF l_in_use_by IS NOT NULL THEN
    log('Step1.4.1 付款单据---------------------------END l_in_use_by:' ||
        l_in_use_by);
    fnd_message.set_name('SQLAP', 'AP_PAY_DOCUMENT_ALREADY_IN_USE');
    l_err_msg := '错误: ' || fnd_message.get;
    log(l_err_msg);
    --RETURN;
  END IF;

  log('Step1.4.2 锁定银行付款单据----------------------------------------BEGIN');
  --锁定银行付款单据
/*  BEGIN
    SELECT last_document_num + 1
      INTO l_check_number
      FROM ap_check_stocks_all
     WHERE check_stock_id = l_check_stock_id
       FOR UPDATE OF last_document_num NOWAIT;
  EXCEPTION
    WHEN OTHERS THEN
      log('锁定单据[' || l_stock_name || ']时异常[' || SQLERRM || ']');
      --RETURN;
  END;

  --检查银行付款单据
  IF nvl(l_check_number, 0) > nvl(l_last_document_num, 0) THEN
    l_err_msg := '付款单据:' || l_stock_name || '的编号已经超过最大允许值.';
    log(l_err_msg);
    --RETURN;
  END IF;*/
  
  l_check_number := to_number(TO_CHAR(SYSDATE,'yyyymmddhh24miss'));
  

  --检查付款编号是否重复
  SELECT COUNT(1)
    INTO l_count
    FROM ap_checks_all aca
   WHERE aca.check_stock_id = l_check_stock_id
     AND aca.check_number = l_check_number;
  log('l_duplicate:' || l_count);
  IF (l_count > 0) THEN
    fnd_message.set_name('SQLAP', 'AP_ALL_DUPLICATE_VALUE');
    l_err_msg := chr(10) || '错误: 付款编号' || l_check_number || ' ' ||
                 fnd_message.get;
    log(l_err_msg);
    --RETURN;
  END IF;
  log('Step1.4.3 检查付款编号是否重复------------------------------------END');

  log('Step1.4 付款单据号[' || l_check_number ||
      ']---------------------------END');

  --check id
  SELECT ap_checks_s.nextval INTO l_check_id FROM dual;

  --checkrun name
  IF l_payment_type_flag = 'Q' THEN
    log('Step1.5 获取批名 --------------BEGIN');
    BEGIN
      SELECT alc.displayed_field
        INTO l_quick_check
        FROM ap_lookup_codes alc
       WHERE alc.lookup_type = 'NLS TRANSLATION'
         AND alc.lookup_code = 'QUICKCHECK ID';
    
    EXCEPTION
      WHEN OTHERS THEN
        log('获取批名前缀时异常[' || SQLERRM || ']');
    END;
    l_checkrun_name := substrb(l_quick_check,
                               1,
                               30 - nvl(lengthb(to_char(l_check_id)), 0)) ||
                       to_char(l_check_id);
    log('Step1.5 获取批名[' || l_checkrun_name || '] --------------END');
  END IF;

  log('Step1.6 获取供应商信息------------------------------------------BEGIN');
  --获取供应商信息
  BEGIN
    SELECT pvs.address_line1,
           pvs.address_line2,
           pvs.address_line3,
           pvs.address_line4,
           nvl(pvs.address_style, 'DEFAULT') address_style,
           pvs.city,
           pvs.county,
           pvs.state,
           pvs.province,
           pvs.zip,
           pvs.country,
           pvs.hold_all_payments_flag,
           pvs.VENDOR_SITE_CODE
      INTO l_address_line1,
           l_address_line2,
           l_address_line3,
           l_address_line4,
           l_address_style,
           l_city,
           l_county,
           l_state,
           l_province,
           l_zip,
           l_country,
           l_hold_all_payments_flag,
           l_VENDOR_SITE_CODE
      FROM po_vendor_sites_all pvs
     WHERE pvs.org_id = l_org_id
       AND pvs.vendor_site_id = l_vendor_site_id;
  EXCEPTION
    WHEN OTHERS THEN
      log('获取供应商地点[' || l_vendor_site_id || ']信息时异常[' || SQLERRM || ']');
  END;

  IF l_hold_all_payments_flag = 'Y' THEN
    l_err_msg := '错误: ' || '当前供应商地点设置了"暂挂所有付款",不能创建付款.';
    log(l_err_msg);
    --RETURN;
  END IF;

  log('Step4.2.5 获取供应商信息------------------------------------------END');

  ----RETURN;

  -- 2.1 插入供应商付款
  log('Step2.1 插入供应商付款-------------------------------------------BEGIN');
  BEGIN
    /*    ap_checks_pkg.insert_row(x_rowid                      => l_row_id,
    x_amount                     => l_invoice_amount,
    x_bank_account_id            => l_bank_account_id,
    x_bank_account_name          => l_bank_account_name,
    x_check_date                 => l_gl_date,
    x_check_id                   => l_check_id,
    x_check_number               => l_check_number,
    x_currency_code              => l_invoice_currency_code,
    x_last_updated_by            => g_user_id,
    x_last_update_date           => SYSDATE,
    x_payment_method_lookup_code => l_payment_method_lookup_code,
    x_payment_type_flag          => l_payment_type_flag,
    x_address_line1              => l_address_line1,
    x_address_line2              => l_address_line2,
    x_address_line3              => l_address_line3,
    x_checkrun_name              => l_checkrun_name,
    x_check_format_id            => l_check_format_id,
    x_check_stock_id             => l_check_stock_id,
    x_city                       => l_city,
    x_country                    => l_country,
    x_created_by                 => g_user_id,
    x_creation_date              => SYSDATE,
    x_last_update_login