Oracle高级PL/SQL

数据的批量处理

游标在处理大批量数据的时候，仅仅启用单核心，会带来性能下降。

通过增强循环语句FORALL和批量绑定语句BULK能够大幅提升性能。

批量绑定

ORACLE的官方文档释意

Oracle Database uses two engines to run PL/SQL units. The PL/SQL engine runs the procedural statements and the SQL engine runs the SQL statements. Every SQL statement causes a context switch between the two engines. You can greatly improve the performance of your database application by minimizing the number of context switches for each PL/SQL unit.

Oracle数据库使用PL/SQL引擎解析过程和函数，使用SQL引擎解析SQL语句，两个引擎相互配合。每个SQL语句都会引起两个引擎之间的上下文切换。如果能够降低两个引擎之间的频繁切换，那么就能最大限度地提升性能，而使用批量绑定语法BULK是能实现。

好处：

BULK 是一次绑定整个集合，通过一次性发送SQL语句供SQL引擎解析的语句。受 SQL 语句影响的行越多，大容量绑定带来的性能提升就越大。考虑使用批量绑定来提高 DML 和 SELECT INTO 引用集合的语句以及 FOR 引用集合并返回 DML 的循环的性能。

实例1：通过bulk批量更新

DECLARE
  TYPE numlist IS VARRAY (100) OF NUMBER;
  id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN
  -- Efficient method, using bulk bind:
  
  FORALL i IN id.FIRST..id.LAST
  UPDATE EMPLOYEES
  SET SALARY = 1.1 * SALARY
  WHERE MANAGER_ID = id(i);
 
 -- Slower method:
 
 FOR i IN id.FIRST..id.LAST LOOP
    UPDATE EMPLOYEES
    SET SALARY = 1.1 * SALARY
    WHERE MANAGER_ID = id(i);
 END LOOP;
END;
/

实例2：配合Returning和FORALL语句实现批量更新

需要返回值的情况下，使用 Returning 指定

DECLARE
  TYPE emp_list IS VARRAY(100) OF EMPLOYEES.EMPLOYEE_ID%TYPE;
  empids emp_list := emp_list(182, 187, 193, 200, 204, 206);
  
  TYPE bonus_list IS TABLE OF EMPLOYEES.SALARY%TYPE;
  bonus_list_inst  bonus_list;
  
BEGIN
  -- Efficient method, using bulk bind:
 
 FORALL i IN empids.FIRST..empids.LAST
 UPDATE EMPLOYEES
 SET SALARY = 0.1 * SALARY
 WHERE EMPLOYEE_ID = empids(i)
 RETURNING SALARY BULK COLLECT INTO bonus_list_inst;
 
 -- Slower method:
 
 FOR i IN empids.FIRST..empids.LAST LOOP
   UPDATE EMPLOYEES
   SET SALARY = 0.1 * SALARY
   WHERE EMPLOYEE_ID = empids(i)
   RETURNING SALARY INTO bonus_list_inst(i);
 END LOOP;
END;
/

数据批量更新

实例

OPEN cur_ap_data;
LOOP
  FETCH cur_ap_data BULK COLLECT
    INTO record_update LIMIT 1000;
  
  FORALL i IN 1 .. record_update.count
    UPDATE inf_gl_voucheritemass
       SET MD_CUSTOMER = NULL,
           MD_supplier = record_update(i).MD_CUSTOMER,
           MD_CFITEM   = record_update(i).MD_CFITEM,
           MD_INDUSTRY = record_update(i).MD_INDUSTRY,
           unitcode    = record_update(i).unitcode,
           vchrnum     = record_update(i).vchrnum
     WHERE 1 = 1
       AND id = record_update(i).id
       AND vchrid = record_update(i).vchrid;
  
  COMMIT;
  
  EXIT WHEN cur_ap_data%NOTFOUND;
END LOOP;
CLOSE cur_ap_data;

【注意】对于头行关联的两张表的数据同步，依然要使用游标进行处理

此外，BULK INTO还能用于DML语句

DECLARE
   TYPE emp_rec_type IS RECORD
   (
      empno      emp.empno%TYPE,
      ename      emp.ename%TYPE,
      hiredate   emp.hiredate%TYPE
   );
   TYPE nested_emp_type IS TABLE OF emp_rec_type;
   emp_tab   nested_emp_type;
BEGIN
   DELETE FROM emp WHERE deptno = 20
   RETURNING empno, ename, hiredate   -- 使用returning 返回这几个列
   BULK   COLLECT INTO emp_tab;       -- 将返回的列的数据批量插入到集合变量

   DBMS_OUTPUT.put_line( '删除 ' || SQL%ROWCOUNT || ' 行记录' );
   COMMIT;

   IF emp_tab.COUNT > 0 THEN   -- 当集合变量不为空时，输出所有被删除的元素
      FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP
         DBMS_OUTPUT.PUT_LINE('当前记录：'
                    || emp_tab( i ).empno || CHR( 9 )
                    || emp_tab( i ).ename || CHR( 9 )
                    || emp_tab( i ).hiredate
                    || ' 已被删除' );
      END LOOP;
   END IF;
END;

‍

数据批量删除

通过forall增强循环删除本地大量的数据

实例：根据id来删除

DECLARE
  l_cnt   NUMBER := 0;
  l_start NUMBER;

  TYPE id_type IS TABLE OF VARCHAR2(255);
  id_tbl id_type;

  CURSOR cur IS
    SELECT t.erp_his_id
      FROM gj_erp_bas_inv_item_his t
     WHERE 1 = 1
       AND t.creation_date <= to_date('2019-12-31', 'yyyy-mm-dd');
BEGIN
  OPEN cur;
  LOOP
  
    FETCH cur BULK COLLECT
      INTO id_tbl LIMIT 1000;
    EXIT WHEN id_tbl.count = 0;
  
    DBMS_OUTPUT.put_line(id_tbl.count || ' rows');
  
    l_start := DBMS_UTILITY.get_time;
    FORALL i IN id_tbl.first .. id_tbl.last
      DELETE FROM gj_erp_bas_inv_item_his WHERE erp_his_id = id_tbl(i);
    COMMIT;
  
    DBMS_OUTPUT.put_line('Delete  : ' || (DBMS_UTILITY.get_time - l_start));
  
    --dbms_lock.sleep(3); 线程休眠需要dba权限
  END LOOP;
END;

‍

数据批量插入

性能比较

实验1：大批量复杂查询

以oracle ebs环境为例，同步应付会计分录，数据集为33万+，获取数据集9秒/100条

循环table类型

使用bulk抓取数据到table类型中，然后循环table，每2000条数据提交一次

1.定义table类型

TYPE l_rec_line IS TABLE OF INF_GL_VOUCHERITEMASS%ROWTYPE;
l_rec_line_list l_rec_line;

2.执行阶段

OPEN cur_voucher_lines;
    LOOP
      FETCH cur_voucher_lines BULK COLLECT
        INTO l_rec_line_list LIMIT 1000;
  
      FORALL i IN l_rec_line_list.first .. l_rec_line_list.last
        INSERT INTO INF_GL_VOUCHERITEMASS VALUES l_rec_line_list (i);
  
      -- 每1000条提交一次
      COMMIT;
  
      EXIT WHEN cur_voucher_lines%NOTFOUND;
    END LOOP;
CLOSE cur_voucher_lines;

3.实验结果

耗时1.5小时，共同步48000条数据，平均每分钟533条数据。

【注意】这里都是按整数批量插入，可能会存在抓取数据集的时候停止同步的过程。

游标单行循环

经典游标单循环

1.实现

FOR rec_line IN cur_voucher_lines LOOP
  ...
END LOOP;

2.实验结果

耗时1.5小时，共同步49144条数据，平均每分钟546条数据。

实验2：化解复杂查询

以oracle ebs环境为例，头和行的全部会计分类同步。

对于行上要获取头上的数据时，除了使用游标进行双重循环来保证行上能获得头上的数据以外，

还可以通过 先同步行，然后同步头，最后头和行关联，批量update的方式达到数据的最终一致性。

游标双重循环

同步应收发票、应收收款、应付发票、应付付款

for rec_header in cur_header loop
  -- 同步头数据
  ...
  
  for rec_line in cur_line loop
   -- 同步行数据
   ...
  end loop;
end loop;

结果：

批量插入与更新

包定义

CREATE OR REPLACE PACKAGE gj_congl_voucher_sync_v2 IS

  /*==================================================
  Procedure Name :
      main
  Description:
      This procedure perform to 集团币种同步
  Argument:
      errbuf               : concurrent return message buffer
      retcode              : concurrent return status code
                             0 success / 1 warning / 2 error
      p_book_id         : 账簿ID
      p_date_from       : 日期从
      p_date_to         : 至
      p_item_from       : 研发项目从
      p_item_to         : 至
  
  History:
      1.00   2020-09-02  LIYIHUI   Creation
  ==================================================*/
  PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT VARCHAR2);

END gj_congl_voucher_sync_v2;

包内容：

CREATE OR REPLACE PACKAGE BODY gj_congl_voucher_sync_v2 IS

  -- Set Who
  g_conc_request_id NUMBER := fnd_global.conc_request_id;
  g_log_msg         VARCHAR2(255);

  -- Global Parameter
  g_pkg_name VARCHAR2(255) := 'gj_congl_voucher_sync';
  g_error    VARCHAR2(20) := fnd_api.g_ret_sts_error;

  /************************************************
  名称：output
  功能描述：
    输出消息
  
  修订记录：
  版本号    编辑时间    编辑人    修改描述
  1.0.0     2020-09-02  LIYIHUI   1.创建
  
  参数描述：
  p_message    IN    传入参数1
  **************************************************/
  PROCEDURE output(p_message_text VARCHAR2) IS
  BEGIN
    IF g_conc_request_id > 0 THEN
      fnd_file.put_line(fnd_file.log, p_message_text);
    ELSE
      dbms_output.put_line(to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') ||
                           ' >>>>>> ' || p_message_text);
    END IF;
  END output;

  /*==================================================
  Procedure Name :
      log
  Description:
      This procedure perform to 日志
  Argument:
      p_message                  : 消息
  History:
      1.00   2020-09-02  LIYIHUI   Creation
  ==================================================*/
  PROCEDURE log(p_message_text VARCHAR2) IS
  BEGIN
    IF g_conc_request_id > 0 THEN
      fnd_file.put_line(fnd_file.log,
                        to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') ||
                        ' >>>>>> ' || p_message_text);
    ELSE
      dbms_output.put_line(to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') ||
                           ' >>>>>> ' || p_message_text);
    END IF;
  END log;

  /*==================================================
  Procedure Name :
      raise_exception
  Description:
      This procedure perform to 抛出异常
  Argument:
      x_return_status             : Input Status : E(Error)/U(Unexception)/S(Success)
  History:
      1.00   2020-09-02  LIYIHUI   Creation
  ==================================================*/
  PROCEDURE raise_exception(x_return_status IN VARCHAR2) IS
  BEGIN
    IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
      RAISE fnd_api.g_exc_unexpected_error;
    ELSIF (x_return_status = fnd_api.g_ret_sts_error) THEN
      RAISE fnd_api.g_exc_error;
    END IF;
  END raise_exception;

  /*==================================================
  Procedure Name :
      raise_exception
  Description:
      This procedure perform to 期初凭证同步
  Argument:
      x_return_status             : Input Status : E(Error)/U(Unexception)/S(Success)
  History:
      1.00   2020-11-17  LIYIHUI   Creation
  ==================================================*/
  PROCEDURE sp_nc_voucher(x_return_status OUT NOCOPY VARCHAR2,
                          x_msg_count     OUT NOCOPY NUMBER,
                          x_msg_data      OUT NOCOPY VARCHAR2) IS
    l_api_name       CONSTANT VARCHAR2(30) := 'ins_sp_nc_voucher';
    l_savepoint_name CONSTANT VARCHAR2(30) := 'sp_nc_voucher';
  
  BEGIN
    -- start activity to create savepoint, check compatibility
    -- and initialize message list, include debug message hint to enter api
    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);
    raise_exception(x_return_status => x_return_status);
  
    INSERT INTO INF_GL_VOUCHER
      (id,
       bookcode,
       unitcode,
       acctyear,
       acctperiod,
       vchrtypecode,
       vchrnum,
       creator,
       createtime,
       createdate,
       postdate,
       postor,
       cashier,
       cashdate,
       attachment,
       adjperiodflag,
       tempflag,
       offsetstate,
       offsetid,
       blueoffsetstate,
       blueoffsetid,
       def_01,
       def_02,
       def_03,
       def_04,
       def_05,
       def_06,
       def_07,
       def_08,
       def_09,
       update_time)
      SELECT * FROM INF_GL_VOUCHER_NC;
  
    INSERT INTO INF_GL_VOUCHERITEMASS
      (id,
       bookcode,
       unitcode,
       subjectcode,
       subjectname,
       ori_subjectcode,
       ori_subjectname,
       currencycode,
       vchrid,
       acctyear,
       acctperiod,
       rowflag,
       vchrtypecode,
       vchrnum,
       createdate,
       postflag,
       debit,
       credit,
       orgnd,
       orgnc,
       qty,
       price,
       exchrate,
       digest,
       bizdate,
       expiredate,
       md_staff,
       md_department,
       md_custsupplier,
       md_customer,
       md_supplier,
       md_industry,
       md_cfitem,
       md_marbasclass,
       md_measurement,
       md_bankaccount,
       md_reason,
       md_project,
       md_contract,
       md_inde_project,
       md_fundplan,
       md_financing,
       def_01,
       def_02,
       def_03,
       def_04,
       def_05,
       def_06,
       def_07,
       def_08,
       def_09,
       update_time)
      SELECT * FROM inf_gl_voucheritemass_NC;
  
    COMMIT;
  
    -- API end body
    -- end activity, include debug message hint to exit api
    x_return_status := cux_api.end_activity(p_pkg_name  => g_pkg_name,
                                            p_api_name  => l_api_name,
                                            x_msg_count => x_msg_count,
                                            x_msg_data  => x_msg_data);
    raise_exception(x_return_status => x_return_status);
  EXCEPTION
    WHEN fnd_api.g_exc_error THEN
      x_return_status := cux_api.handle_exceptions(p_pkg_name       => g_pkg_name,
                                                   p_api_name       => l_api_name,
                                                   p_savepoint_name => l_savepoint_name,
                                                   p_exc_name       => cux_api.g_exc_name_error,
                                                   x_msg_count      => x_msg_count,
                                                   x_msg_data       => x_msg_data);
    WHEN fnd_api.g_exc_unexpected_error THEN
      x_return_status := cux_api.handle_exceptions(p_pkg_name       => g_pkg_name,
                                                   p_api_name       => l_api_name,
                                                   p_savepoint_name => l_savepoint_name,
                                                   p_exc_name       => cux_api.g_exc_name_unexp,
                                                   x_msg_count      => x_msg_count,
                                                   x_msg_data       => x_msg_data);
    WHEN OTHERS THEN
      x_return_status := cux_api.handle_exceptions(p_pkg_name       => g_pkg_name,
                                                   p_api_name       => l_api_name,
                                                   p_savepoint_name => l_savepoint_name,
                                                   p_exc_name       => cux_api.g_exc_name_others,
                                                   x_msg_count      => x_msg_count,
                                                   x_msg_data       => x_msg_data);
  END sp_nc_voucher;

  /**
  * @Author  : KALOSORA
  * @Purpose : 同步会计分录行，根据行的accounting_date反过来匹配头的数据
  *            可能会存在如下情况
  *              ap_invoices_all.gl_date < 2020-01-01
  *            但此时存在冲销、取消的业务
  *              xla_ae_lines.accounting_date > 2020-01-01
  *
  * @Param   ：x_return_status 返回的参数状态
  * @Param   ：x_return_status 返回的消息
  * @Version : 1.0
  * @Since   : 2021/1/19 9:12:35
  */
  PROCEDURE sp_vchrnum_lines(x_return_status OUT NOCOPY VARCHAR2,
                             x_msg_count     OUT NOCOPY NUMBER,
                             x_msg_data      OUT NOCOPY VARCHAR2) IS
    l_api_name CONSTANT VARCHAR2(100) := 'sp_vchrnum_lines';
  
    TYPE l_rec_line IS TABLE OF INF_GL_VOUCHERITEMASS%ROWTYPE;
    l_rec_line_list l_rec_line;
  
    CURSOR cur_voucher_lines IS
      SELECT to_char(xal.AE_HEADER_ID) || LPAD(xal.AE_LINE_NUM, 4, 0) id,
             'GJ_CN_01' BOOKCODE,
             'PENDING' UNITCODE,
             --xep.LEGAL_ENTITY_IDENTIFIER UNITCODE,
             (SELECT t.org_coa_acc_code
                FROM gj_organization_coa_flex_value t
               WHERE 1 = 1
                 AND t.COA_ACC_CODE = gcck.segment3) SUBJECTCODE,
             (SELECT t.org_coa_acc_desc
                FROM gj_organization_coa_flex_value t
               WHERE 1 = 1
                 AND t.COA_ACC_CODE = gcck.segment3) SUBJECTNAME,
             gcck.segment3 ORI_SUBJECTCODE,
             gj_account_details_rpt.get_flex_description('GJ_COA_ACC',
                                                         gcck.segment3) ORI_SUBJECTNAME,
             xal.CURRENCY_CODE CURRENCYCODE,
             ora_hash(xte.entity_code) || to_char(xte.source_id_int_1) VCHRID,
             to_number(to_char(xal.ACCOUNTING_DATE, 'yyyy')) ACCTYEAR,
             to_char(xal.ACCOUNTING_DATE, 'mm') ACCTPERIOD,
             xal.ae_line_num ROWFLAG,
             decode(xte.entity_code,
                    'AP_INVOICES',
                    '2',
                    'AP_PAYMENTS',
                    '3',
                    'RECEIPTS',
                    '5',
                    'TRANSACTIONS',
                    '4') vchrtypecode,
             /*(SELECT to_char(aia.voucher_num)
              FROM ap_invoices_all aia
             WHERE 1 = 1
               AND ora_hash(xte.entity_code) || to_char(aia.invoice_id) =
                   ora_hash(xte.entity_code) || to_char(xte.source_id_int_1)
               AND rownum = 1) vchrnum,*/
             'PENDING' vchrnum,
             to_char(xal.ACCOUNTING_DATE, 'yyyy-mm-dd hh24:mi:ss') createdate,
             'Y' postflag,
             (DECODE((SELECT goc.attribute2
                       FROM gj_organization_coa_flex_value t,
                            gj_organization_coa_flex_type  goc
                      WHERE 1 = 1
                        AND t.org_coa_acc_code = goc.org_coa_code
                        AND t.COA_ACC_CODE = gcck.segment3
                        AND goc.attribute1 IN ('成本类', '损益类')),
                     '贷方',
                     0,
                     '借方',
                     nvl(xal.accounted_cr * -1, 0) +
                     nvl(xal.accounted_dr, 0),
                     nvl(xal.accounted_dr, 0))) DEBIT,
             (DECODE((SELECT goc.attribute2
                       FROM gj_organization_coa_flex_value t,
                            gj_organization_coa_flex_type  goc
                      WHERE 1 = 1
                        AND t.org_coa_acc_code = goc.org_coa_code
                        AND t.COA_ACC_CODE = gcck.segment3
                        AND goc.attribute1 IN ('成本类', '损益类')),
                     '借方',
                     0,
                     '贷方',
                     nvl(xal.accounted_dr * -1, 0) +
                     nvl(xal.accounted_cr, 0),
                     nvl(xal.accounted_cr, 0))) CREDIT,
             (DECODE((SELECT goc.attribute2
                       FROM gj_organization_coa_flex_value t,
                            gj_organization_coa_flex_type  goc
                      WHERE 1 = 1
                        AND t.org_coa_acc_code = goc.org_coa_code
                        AND t.COA_ACC_CODE = gcck.segment3
                        AND goc.attribute1 IN ('成本类', '损益类')),
                     '贷方',
                     0,
                     '借方',
                     nvl(xal.accounted_cr * -1, 0) +
                     nvl(xal.accounted_dr, 0),
                     nvl(xal.accounted_dr, 0))) ORGND,
             (DECODE((SELECT goc.attribute2
                       FROM gj_organization_coa_flex_value t,
                            gj_organization_coa_flex_type  goc
                      WHERE 1 = 1
                        AND t.org_coa_acc_code = goc.org_coa_code
                        AND t.COA_ACC_CODE = gcck.segment3
                        AND goc.attribute1 IN ('成本类', '损益类')),
                     '借方',
                     0,
                     '贷方',
                     nvl(xal.accounted_dr * -1, 0) +
                     nvl(xal.accounted_cr, 0),
                     nvl(xal.accounted_cr, 0))) ORGNC,
             0 QTY,
             0 PRICE,
             decode(xal.CURRENCY_CODE,
                    'CNY',
                    1,
                    nvl(xal.CURRENCY_CONVERSION_RATE, 1)) EXCHRATE,
             nvl(nvl(xal.description, xah.DESCRIPTION), '入账') DIGEST,
             to_date(xal.ACCOUNTING_DATE, 'yyyy-mm-dd hh24:mi:ss') bizdate,
             NULL expiredate,
             NULL md_staff,
             DECODE(gcck.segment2, '0', NULL, SUBSTR(gcck.segment2, 1, 4)) MD_DEPARTMENT,
             NULL md_custsupplier,
             'PENDING' MD_CUSTOMER,
             --xal.PARTY_TYPE_CODE || pv.segment1 MD_SUPPLIER,
             'PENDING' MD_SUPPLIER,
             /*DECODE((SELECT ffvs.FLEX_VALUE
               FROM fnd_flex_values_vl ffvs, fnd_flex_value_sets ffv
              WHERE 1 = 1
                AND ffvs.FLEX_VALUE_SET_ID = ffv.FLEX_VALUE_SET_ID
                AND ffv.FLEX_VALUE_SET_NAME = 'GJ_INTER_VIM_INDUSTRY'
                AND ffvs.FLEX_VALUE = SUBSTR(gcck.segment3, 1, 4)
                AND ffvs.ENABLED_FLAG = 'Y'
                AND nvl(ffvs.END_DATE_ACTIVE, SYSDATE) >= SYSDATE),
             NULL,
             NULL,
             (SELECT flv.DESCRIPTION
                FROM fnd_lookup_values flv
               WHERE 1 = 1
                 AND flv.LOOKUP_TYPE = 'GJ_INTER_LEGAL_TYPE'
                 AND flv.LANGUAGE = 'ZHS'
                 AND flv.ENABLED_FLAG = 'Y'
                 AND nvl(flv.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
                 AND flv.LOOKUP_CODE = xep.LEGAL_ENTITY_IDENTIFIER)) MD_INDUSTRY,*/
             'PENDING' MD_INDUSTRY,
             /*(SELECT aid.attribute1
              FROM ap_invoices_all aia, ap_invoice_distributions_all aid
             WHERE 1 = 1
               AND aia.invoice_id = aid.invoice_id
               AND ora_hash(xte.entity_code) || to_char(aia.invoice_id) =
                   ora_hash(xte.entity_code) || to_char(xte.source_id_int_1)
               AND rownum = 1) MD_CFITEM,*/
             'PENDING' MD_CFITEM,
             DECODE((SELECT ffvs.FLEX_VALUE
                      FROM fnd_flex_values_vl ffvs, fnd_flex_value_sets ffv
                     WHERE 1 = 1
                       AND ffvs.FLEX_VALUE_SET_ID = ffv.FLEX_VALUE_SET_ID
                       AND ffv.FLEX_VALUE_SET_NAME =
                           'GJ_INTER_VIM_MARBASCLASS'
                       AND ffvs.FLEX_VALUE = SUBSTR(gcck.segment3, 1, 4)
                       AND ffvs.ENABLED_FLAG = 'Y'
                       AND nvl(ffvs.END_DATE_ACTIVE, SYSDATE) >= SYSDATE),
                    NULL,
                    NULL,
                    (SELECT flv.DESCRIPTION
                       FROM fnd_lookup_values flv
                      WHERE 1 = 1
                        AND flv.LOOKUP_TYPE = 'GJ_INTER_MARBASCLASS'
                        AND flv.LANGUAGE = 'ZHS'
                        AND flv.ENABLED_FLAG = 'Y'
                        AND nvl(flv.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
                        AND flv.LOOKUP_CODE = gcck.segment1)) MD_MARBASCLASS,
             NULL md_measurement,
             gcck.segment4 MD_BANKACCOUNT,
             NULL MD_REASON,
             NULL md_project,
             NULL md_contract,
             NULL md_inde_project,
             NULL MD_FUNDPLAN,
             NULL MD_FINANCING,
             NULL def_01,
             NULL def_02,
             NULL def_03,
             NULL def_04,
             NULL def_05,
             NULL def_06,
             NULL def_07,
             NULL def_08,
             NULL def_09,
             to_date(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') update_time
        FROM xla_ae_headers               xah,
             xla_ae_lines                 xal,
             xla.xla_transaction_entities xte,
             gl_code_combinations_kfv     gcck,
             --
             fnd_lookup_values flv
       WHERE 1 = 1
         AND xah.AE_HEADER_ID = xal.AE_HEADER_ID
         AND xte.ledger_id = 2021
         AND xte.entity_id = xah.entity_id
         AND (nvl(accounted_cr, 0) <> 0 OR nvl(accounted_dr, 0) <> 0 OR
             fnd_profile.value('XLA_SHOW_ZERO_AMT_JRNL') = 'Y')
         AND xte.application_id IN (200, 222) --应付/应收模块
         AND xte.application_id = xah.application_id
         AND xte.entity_code IN
             ('AP_INVOICES', 'AP_PAYMENTS', 'RECEIPTS', 'TRANSACTIONS')
         AND xal.code_combination_id = gcck.code_combination_id
            --最终过账
         AND flv.LANGUAGE = 'ZHS'
         AND flv.VIEW_APPLICATION_ID = 602
         AND flv.lookup_type = 'XLA_ACCOUNTING_ENTRY_STATUS'
         AND flv.meaning = '最终'
         AND flv.lookup_code = xah.accounting_entry_status_code
            -- 参数
         AND xal.ACCOUNTING_DATE >= to_date('2020-01-01', 'yyyy-mm-dd')
            -- 同步的ou参数
         AND EXISTS
       (SELECT 1
                FROM fnd_lookup_values flv, hr_operating_units hou
               WHERE 1 = 1
                 AND flv.LOOKUP_TYPE = 'GJ_INTER_COM'
                 AND flv.LANGUAGE = 'ZHS'
                 AND nvl(flv.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
                 AND flv.ENABLED_FLAG = 'Y'
                 AND hou.short_code = gcck.segment1);
  
  BEGIN
    -- 日志开始
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  
    OPEN cur_voucher_lines;
    LOOP
      FETCH cur_voucher_lines BULK COLLECT
        INTO l_rec_line_list LIMIT 1000;
  
      FORALL i IN l_rec_line_list.first .. l_rec_line_list.last
        INSERT INTO INF_GL_VOUCHERITEMASS VALUES l_rec_line_list (i);
  
      -- 每1000条提交一次
      COMMIT;
  
      EXIT WHEN cur_voucher_lines%NOTFOUND;
    END LOOP;
    CLOSE cur_voucher_lines;
  
    -- 日志结束
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  EXCEPTION
    WHEN OTHERS THEN
      x_return_status := g_error;
      x_msg_data      := l_api_name || '->' || SQLERRM ||
                         dbms_utility.format_error_backtrace;
      log(x_msg_data);
  END sp_vchrnum_lines;

  /**
  * @Author  : KALOSORA
  * @Purpose : 根据已经同步的会计分录行，匹配应付发票头数据
  *
  * @Param   ：x_return_status 返回的参数状态
  * @Param   ：x_return_status 返回的消息
  * @Version : 1.0
  * @Since   : 2021/1/19 9:12:35
  */
  PROCEDURE sp_ap_invoice_headers(x_return_status OUT NOCOPY VARCHAR2,
                                  x_msg_count     OUT NOCOPY NUMBER,
                                  x_msg_data      OUT NOCOPY VARCHAR2) IS
    l_api_name CONSTANT VARCHAR2(100) := 'sp_ap_invoice_headers';
  
    TYPE l_rec_header IS TABLE OF INF_GL_VOUCHER%ROWTYPE;
    l_rec_header_list l_rec_header;
  
    CURSOR cur_invoice_headers IS
      SELECT infvhr.id,
             'GJ_CN_01' BOOKCODE,
             xep.LEGAL_ENTITY_IDENTIFIER UNITCODE,
             infvhr.ACCTYEAR,
             infvhr.ACCTPERIOD,
             infvhr.VCHRTYPECODE,
             infvhr.VCHRNUM,
             fu.USER_NAME CREATOR,
             infvhr.CREATETIME,
             infvhr.CREATEDATE,
             infvhr.POSTDATE,
             NULL POSTOR,
             NULL CASHIER,
             NULL CASHDATE,
             NULL ATTACHMENT,
             infvhr.ADJPERIODFLAG,
             '0' TEMPFLAG,
             '0' OFFSETSTATE,
             NULL OFFSETID,
             '0' BLUEOFFSETSTATE,
             NULL BLUEOFFSETID,
             infvhr.MD_SUPPLIER def_01,
             infvhr.MD_CFITEM def_02,
             NULL def_03,
             NULL def_04,
             NULL def_05,
             NULL def_06,
             NULL def_07,
             NULL def_08,
             NULL def_09,
             to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') update_time
        FROM (SELECT ora_hash('AP_INVOICES') || aia.invoice_id ID,
                     aia.org_id,
                     aia.invoice_id,
                     aia.CREATED_BY,
                     to_number(to_char(aia.GL_DATE, 'yyyy')) ACCTYEAR,
                     to_char(aia.GL_DATE, 'mm') ACCTPERIOD,
                     (SELECT flv.LOOKUP_CODE
                        FROM fnd_lookup_values flv
                       WHERE 1 = 1
                         AND flv.LOOKUP_TYPE = 'GJ_INTER_MD_VCHRTYPE'
                         AND flv.LANGUAGE = 'ZHS'
                         AND flv.ENABLED_FLAG = 'Y'
                         AND NVL(flv.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
                         AND flv.MEANING = '应付发票') VCHRTYPECODE,
                     nvl(to_char(aia.VOUCHER_NUM),
                         GJ_CONGL_UTIL.get_virtual_voucher) VCHRNUM,
                     to_char(aia.GL_DATE, 'yyyy-mm-dd hh24:mi:ss') CREATETIME,
                     to_char(aia.GL_DATE, 'yyyy-mm-dd hh24:mi:ss') CREATEDATE,
                     to_char(aia.GL_DATE, 'yyyy-mm-dd hh24:mi:ss') POSTDATE,
                     '0' ADJPERIODFLAG,
                     'S' || pv.segment1 MD_SUPPLIER,
                     (SELECT aid.attribute1
                        FROM ap_invoice_distributions_all aid
                       WHERE 1 = 1
                         AND aid.INVOICE_ID = aia.invoice_id
                         AND rownum = 1) MD_CFITEM
                FROM ap_invoices_all aia, po_vendors pv
               WHERE 1 = 1
                 AND pv.vendor_id = aia.vendor_id) infvhr,
             hr_operating_units hou,
             xle_entity_profiles xep,
             fnd_user fu
       WHERE 1 = 1
         AND xep.LEGAL_ENTITY_ID = hou.default_legal_context_id
         AND hou.organization_id = infvhr.org_id
         AND fu.user_id = infvhr.created_by
         AND EXISTS (SELECT 1
                FROM INF_GL_VOUCHERITEMASS igv
               WHERE 1 = 1
                 AND igv.vchrtypecode = infvhr.vchrtypecode
                 AND igv.vchrtypecode = '2'
                 AND igv.vchrid = infvhr.id);
  BEGIN
    -- 日志开始
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  
    OPEN cur_invoice_headers;
    LOOP
      FETCH cur_invoice_headers BULK COLLECT
        INTO l_rec_header_list LIMIT 1000;
  
      FORALL i IN l_rec_header_list.first .. l_rec_header_list.last
        INSERT INTO INF_GL_VOUCHER VALUES l_rec_header_list (i);
  
      -- 每1000条提交一次
      COMMIT;
  
      EXIT WHEN cur_invoice_headers%NOTFOUND;
    END LOOP;
    CLOSE cur_invoice_headers;
  
    -- 日志结束
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  
  EXCEPTION
    WHEN OTHERS THEN
      x_return_status := g_error;
      x_msg_data      := l_api_name || '->' || SQLERRM ||
                         dbms_utility.format_error_backtrace;
      log(x_msg_data);
  END sp_ap_invoice_headers;

  /**
  * @Author  : KALOSORA
  * @Purpose : 根据已经同步的会计分录行，匹配应付付款头数据
  *
  * @Param   ：x_return_status 返回的参数状态
  * @Param   ：x_return_status 返回的消息
  * @Version : 1.0
  * @Since   : 2021/1/19 9:12:35
  */
  PROCEDURE sp_ap_payment_headers(x_return_status OUT NOCOPY VARCHAR2,
                                  x_msg_count     OUT NOCOPY NUMBER,
                                  x_msg_data      OUT NOCOPY VARCHAR2) IS
    l_api_name CONSTANT VARCHAR2(100) := 'sp_ap_payment_headers';
  
    TYPE l_rec_header IS TABLE OF INF_GL_VOUCHER%ROWTYPE;
    l_rec_header_list l_rec_header;
  
    CURSOR cur_payment_headers IS
      SELECT infvhr.id,
             'GJ_CN_01' BOOKCODE,
             xep.LEGAL_ENTITY_IDENTIFIER UNITCODE,
             infvhr.ACCTYEAR,
             infvhr.ACCTPERIOD,
             infvhr.VCHRTYPECODE,
             infvhr.VCHRNUM,
             fu.USER_NAME CREATOR,
             infvhr.CREATETIME,
             infvhr.CREATEDATE,
             infvhr.POSTDATE,
             NULL POSTOR,
             NULL CASHIER,
             NULL CASHDATE,
             NULL ATTACHMENT,
             infvhr.ADJPERIODFLAG,
             '0' TEMPFLAG,
             '0' OFFSETSTATE,
             NULL OFFSETID,
             '0' BLUEOFFSETSTATE,
             NULL BLUEOFFSETID,
             infvhr.MD_SUPPLIER def_01,
             infvhr.MD_CFITEM def_02,
             NULL def_03,
             NULL def_04,
             NULL def_05,
             NULL def_06,
             NULL def_07,
             NULL def_08,
             NULL def_09,
             to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') update_time
        FROM (SELECT ora_hash('AP_PAYMENTS') || aca.check_id ID,
                     aca.org_id,
                     aca.check_id,
                     aca.CREATED_BY,
                     to_number(to_char(aca.CHECK_DATE, 'yyyy')) ACCTYEAR,
                     to_char(aca.CHECK_DATE, 'mm') ACCTPERIOD,
                     (SELECT flv.LOOKUP_CODE
                        FROM fnd_lookup_values flv
                       WHERE 1 = 1
                         AND flv.LOOKUP_TYPE = 'GJ_INTER_MD_VCHRTYPE'
                         AND flv.LANGUAGE = 'ZHS'
                         AND flv.ENABLED_FLAG = 'Y'
                         AND NVL(flv.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
                         AND flv.MEANING = '应付付款') VCHRTYPECODE,
                     nvl(to_char(aca.DOC_SEQUENCE_VALUE),
                         GJ_CONGL_UTIL.get_virtual_voucher) VCHRNUM,
                     to_char(aca.CHECK_DATE, 'yyyy-mm-dd hh24:mi:ss') CREATETIME,
                     to_char(aca.CHECK_DATE, 'yyyy-mm-dd hh24:mi:ss') CREATEDATE,
                     to_char(aca.CHECK_DATE, 'yyyy-mm-dd hh24:mi:ss') POSTDATE,
                     '0' ADJPERIODFLAG,
                     'S' || pv.segment1 MD_SUPPLIER,
                     aca.attribute4 MD_CFITEM
                FROM ap_checks_all aca， po_vendors pv
               WHERE 1 = 1
                 AND pv.VENDOR_ID = aca.vendor_id) infvhr,
             hr_operating_units hou,
             xle_entity_profiles xep,
             fnd_user fu
       WHERE 1 = 1
         AND xep.LEGAL_ENTITY_ID = hou.default_legal_context_id
         AND hou.organization_id = infvhr.org_id
         AND fu.user_id = infvhr.created_by
         AND EXISTS (SELECT 1
                FROM INF_GL_VOUCHERITEMASS igv
               WHERE 1 = 1
                 AND igv.vchrtypecode = infvhr.vchrtypecode
                 AND igv.vchrtypecode = '3'
                 AND igv.vchrid = infvhr.id);
  BEGIN
  
    -- 日志开始
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  
    OPEN cur_payment_headers;
    LOOP
      FETCH cur_payment_headers BULK COLLECT
        INTO l_rec_header_list LIMIT 1000;
  
      FORALL i IN l_rec_header_list.first .. l_rec_header_list.last
        INSERT INTO INF_GL_VOUCHER VALUES l_rec_header_list (i);
  
      -- 每1000条提交一次
      COMMIT;
  
      EXIT WHEN cur_payment_headers%NOTFOUND;
    END LOOP;
    CLOSE cur_payment_headers;
  
    -- 日志结束
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  EXCEPTION
    WHEN OTHERS THEN
      x_return_status := g_error;
      x_msg_data      := l_api_name || '->' || SQLERRM ||
                         dbms_utility.format_error_backtrace;
      log(x_msg_data);
  END sp_ap_payment_headers;

  /**
  * @Author  : KALOSORA
  * @Purpose : 根据已经同步的会计分录行，匹配应收发票头数据
  *
  * @Param   ：x_return_status 返回的参数状态
  * @Param   ：x_return_status 返回的消息
  * @Version : 1.0
  * @Since   : 2021/1/19 9:12:35
  */
  PROCEDURE sp_ar_invoice_headers(x_return_status OUT NOCOPY VARCHAR2,
                                  x_msg_count     OUT NOCOPY NUMBER,
                                  x_msg_data      OUT NOCOPY VARCHAR2) IS
    l_api_name CONSTANT VARCHAR2(100) := 'sp_ar_invoice_headers';
  
    TYPE l_rec_header IS TABLE OF INF_GL_VOUCHER%ROWTYPE;
    l_rec_header_list l_rec_header;
  
    CURSOR cur_transaction_headers IS
      SELECT infvhr.id,
             'GJ_CN_01' BOOKCODE,
             xep.LEGAL_ENTITY_IDENTIFIER UNITCODE,
             infvhr.ACCTYEAR,
             infvhr.ACCTPERIOD,
             infvhr.VCHRTYPECODE,
             infvhr.VCHRNUM,
             fu.USER_NAME CREATOR,
             infvhr.CREATETIME,
             infvhr.CREATEDATE,
             infvhr.POSTDATE,
             NULL POSTOR,
             NULL CASHIER,
             NULL CASHDATE,
             NULL ATTACHMENT,
             infvhr.ADJPERIODFLAG,
             '0' TEMPFLAG,
             '0' OFFSETSTATE,
             NULL OFFSETID,
             '0' BLUEOFFSETSTATE,
             NULL BLUEOFFSETID,
             infvhr.MD_CUSTOMER def_01,
             infvhr.MD_CFITEM def_02,
             NULL def_03,
             NULL def_04,
             NULL def_05,
             NULL def_06,
             NULL def_07,
             NULL def_08,
             NULL def_09,
             to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') update_time
        FROM (SELECT ora_hash('TRANSACTIONS') || rct.CUSTOMER_TRX_ID id,
                     rct.ORG_ID,
                     rct.CUSTOMER_TRX_ID CUSTOMER_TRX_ID,
                     rct.CREATED_BY,
                     to_number(to_char((SELECT rctlgd.GL_DATE
                                         FROM ra_cust_trx_line_gl_dist_all rctlgd
                                        WHERE 1 = 1
                                          AND rctlgd.CUSTOMER_TRX_ID =
                                              rct.CUSTOMER_TRX_ID
                                          AND rctlgd.gl_date IS NOT NULL
                                          AND rownum = 1),
                                       'yyyy')) ACCTYEAR,
                     to_char((SELECT rctlgd.GL_DATE
                               FROM ra_cust_trx_line_gl_dist_all rctlgd
                              WHERE 1 = 1
                                AND rctlgd.CUSTOMER_TRX_ID =
                                    rct.CUSTOMER_TRX_ID
                                AND rctlgd.gl_date IS NOT NULL
                                AND rownum = 1),
                             'mm') ACCTPERIOD,
                     (SELECT flv.LOOKUP_CODE
                        FROM fnd_lookup_values flv
                       WHERE 1 = 1
                         AND flv.LOOKUP_TYPE = 'GJ_INTER_MD_VCHRTYPE'
                         AND flv.LANGUAGE = 'ZHS'
                         AND flv.ENABLED_FLAG = 'Y'
                         AND NVL(flv.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
                         AND flv.MEANING = '应收发票') VCHRTYPECODE,
                     nvl(to_char(rct.DOC_SEQUENCE_VALUE),
                         GJ_CONGL_UTIL.get_virtual_voucher) VCHRNUM,
                     to_char((SELECT rctlgd.GL_DATE
                               FROM ra_cust_trx_line_gl_dist_all rctlgd
                              WHERE 1 = 1
                                AND rctlgd.CUSTOMER_TRX_ID =
                                    rct.CUSTOMER_TRX_ID
                                AND rctlgd.GL_DATE IS NOT NULL
                                AND rownum = 1),
                             'yyyy-mm-dd hh24:mi:ss') CREATETIME,
                     to_char((SELECT rctlgd.GL_DATE
                               FROM ra_cust_trx_line_gl_dist_all rctlgd
                              WHERE 1 = 1
                                AND rctlgd.CUSTOMER_TRX_ID =
                                    rct.CUSTOMER_TRX_ID
                                AND rctlgd.GL_DATE IS NOT NULL
                                AND rownum = 1),
                             'yyyy-mm-dd hh24:mi:ss') CREATEDATE,
                     to_char((SELECT rctlgd.GL_DATE
                               FROM ra_cust_trx_line_gl_dist_all rctlgd
                              WHERE 1 = 1
                                AND rctlgd.CUSTOMER_TRX_ID =
                                    rct.CUSTOMER_TRX_ID
                                AND rctlgd.GL_DATE IS NOT NULL
                                AND rownum = 1),
                             'yyyy-mm-dd hh24:mi:ss') POSTDATE,
                     '0' ADJPERIODFLAG,
                     'C' || hca.ACCOUNT_NUMBER MD_CUSTOMER,
                     NULL MD_CFITEM
                FROM ra_customer_trx_all rct,
                     hz_cust_accounts    hca,
                     hz_parties          hp
               WHERE 1 = 1
                 AND hca.cust_account_id = rct.BILL_TO_CUSTOMER_ID
                 AND hca.PARTY_ID = hp.PARTY_ID) infvhr,
             hr_operating_units hou,
             xle_entity_profiles xep,
             fnd_user fu
       WHERE 1 = 1
         AND xep.LEGAL_ENTITY_ID = hou.default_legal_context_id
         AND hou.organization_id = infvhr.org_id
         AND fu.user_id = infvhr.created_by
         AND EXISTS (SELECT 1
                FROM INF_GL_VOUCHERITEMASS igv
               WHERE 1 = 1
                 AND igv.vchrtypecode = infvhr.vchrtypecode
                 AND igv.vchrtypecode = '4'
                 AND igv.vchrid = infvhr.id);
  BEGIN
  
    -- 日志开始
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  
    OPEN cur_transaction_headers;
    LOOP
      FETCH cur_transaction_headers BULK COLLECT
        INTO l_rec_header_list LIMIT 1000;
  
      FORALL i IN l_rec_header_list.first .. l_rec_header_list.last
        INSERT INTO INF_GL_VOUCHER VALUES l_rec_header_list (i);
  
      -- 每1000条提交一次
      COMMIT;
  
      EXIT WHEN cur_transaction_headers%NOTFOUND;
    END LOOP;
    CLOSE cur_transaction_headers;
  
    -- 日志结束
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  EXCEPTION
    WHEN OTHERS THEN
      x_return_status := g_error;
      x_msg_data      := l_api_name || '->' || SQLERRM ||
                         dbms_utility.format_error_backtrace;
      log(x_msg_data);
  END sp_ar_invoice_headers;

  /**
  * @Author  : KALOSORA
  * @Purpose : 根据已经同步的会计分录行，匹配应收收款头数据
  *
  * @Param   ：x_return_status 返回的参数状态
  * @Param   ：x_return_status 返回的消息
  * @Version : 1.0
  * @Since   : 2021/1/19 9:12:35
  */
  PROCEDURE sp_ar_receipt_headers(x_return_status OUT NOCOPY VARCHAR2,
                                  x_msg_count     OUT NOCOPY NUMBER,
                                  x_msg_data      OUT NOCOPY VARCHAR2) IS
    l_api_name CONSTANT VARCHAR2(100) := 'sp_ar_receipt_headers';
  
    TYPE l_rec_header IS TABLE OF INF_GL_VOUCHER%ROWTYPE;
    l_rec_header_list l_rec_header;
  
    CURSOR cur_receipt_headers IS
      SELECT infvhr.id,
             'GJ_CN_01' BOOKCODE,
             xep.LEGAL_ENTITY_IDENTIFIER UNITCODE,
             infvhr.ACCTYEAR,
             infvhr.ACCTPERIOD,
             infvhr.VCHRTYPECODE,
             infvhr.VCHRNUM,
             fu.USER_NAME CREATOR,
             infvhr.CREATETIME,
             infvhr.CREATEDATE,
             infvhr.POSTDATE,
             NULL POSTOR,
             NULL CASHIER,
             NULL CASHDATE,
             NULL ATTACHMENT,
             infvhr.ADJPERIODFLAG,
             '0' TEMPFLAG,
             '0' OFFSETSTATE,
             NULL OFFSETID,
             '0' BLUEOFFSETSTATE,
             NULL BLUEOFFSETID,
             infvhr.MD_CUSTOMER def_01,
             infvhr.MD_CFITEM def_02,
             NULL def_03,
             NULL def_04,
             NULL def_05,
             NULL def_06,
             NULL def_07,
             NULL def_08,
             NULL def_09,
             to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') update_time
        FROM (SELECT ora_hash('RECEIPTS') || acr.CASH_RECEIPT_ID id,
                     acr.ORG_ID,
                     acr.CASH_RECEIPT_ID,
                     acr.CREATED_BY,
                     to_number(to_char((SELECT acrh.GL_DATE
                                         FROM AR_CASH_RECEIPT_HISTORY_ALL acrh
                                        WHERE acrh.CASH_RECEIPT_ID =
                                              acr.CASH_RECEIPT_ID
                                          AND acrh.org_id = acr.org_id
                                          AND acrh.FIRST_POSTED_RECORD_FLAG(+) = 'Y'),
                                       'yyyy')) ACCTYEAR,
                     to_char((SELECT acrh.GL_DATE
                               FROM AR_CASH_RECEIPT_HISTORY_ALL acrh
                              WHERE acrh.CASH_RECEIPT_ID =
                                    acr.CASH_RECEIPT_ID
                                AND acrh.org_id = acr.org_id
                                AND acrh.FIRST_POSTED_RECORD_FLAG(+) = 'Y'),
                             'mm') ACCTPERIOD,
                     (SELECT flv.LOOKUP_CODE
                        FROM fnd_lookup_values flv
                       WHERE 1 = 1
                         AND flv.LOOKUP_TYPE = 'GJ_INTER_MD_VCHRTYPE'
                         AND flv.LANGUAGE = 'ZHS'
                         AND flv.ENABLED_FLAG = 'Y'
                         AND NVL(flv.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
                         AND flv.MEANING = '应收收款') VCHRTYPECODE,
                     nvl(to_char(acr.DOC_SEQUENCE_VALUE),
                         GJ_CONGL_UTIL.get_virtual_voucher) VCHRNUM,
                     to_char((SELECT acrh.GL_DATE
                               FROM AR_CASH_RECEIPT_HISTORY_ALL acrh
                              WHERE acrh.CASH_RECEIPT_ID =
                                    acr.CASH_RECEIPT_ID
                                AND acrh.org_id = acr.org_id
                                AND acrh.FIRST_POSTED_RECORD_FLAG(+) = 'Y'),
                             'yyyy-mm-dd hh24:mi:ss') CREATETIME,
                     to_char((SELECT acrh.GL_DATE
                               FROM AR_CASH_RECEIPT_HISTORY_ALL acrh
                              WHERE acrh.CASH_RECEIPT_ID =
                                    acr.CASH_RECEIPT_ID
                                AND acrh.org_id = acr.org_id
                                AND acrh.FIRST_POSTED_RECORD_FLAG(+) = 'Y'),
                             'yyyy-mm-dd hh24:mi:ss') CREATEDATE,
                     to_char((SELECT acrh.GL_DATE
                               FROM AR_CASH_RECEIPT_HISTORY_ALL acrh
                              WHERE acrh.CASH_RECEIPT_ID =
                                    acr.CASH_RECEIPT_ID
                                AND acrh.org_id = acr.org_id
                                AND acrh.FIRST_POSTED_RECORD_FLAG(+) = 'Y'),
                             'yyyy-mm-dd hh24:mi:ss') POSTDATE,
                     '0' ADJPERIODFLAG,
                     'C' || hca.ACCOUNT_NUMBER MD_CUSTOMER,
                     '1111' MD_CFITEM
                FROM ar_cash_receipts_all acr,
                     hz_cust_accounts     hca,
                     hz_parties           hp
               WHERE 1 = 1
                 AND acr.PAY_FROM_CUSTOMER = hca.cust_account_id
                 AND hca.PARTY_ID = hp.PARTY_ID) infvhr,
             hr_operating_units hou,
             xle_entity_profiles xep,
             fnd_user fu
       WHERE 1 = 1
         AND xep.LEGAL_ENTITY_ID = hou.default_legal_context_id
         AND hou.organization_id = infvhr.org_id
         AND fu.user_id = infvhr.created_by
         AND EXISTS (SELECT 1
                FROM INF_GL_VOUCHERITEMASS igv
               WHERE 1 = 1
                 AND igv.vchrtypecode = infvhr.vchrtypecode
                 AND igv.vchrtypecode = '5'
                 AND igv.vchrid = infvhr.id);
  BEGIN
    -- 日志开始
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  
    OPEN cur_receipt_headers;
    LOOP
      FETCH cur_receipt_headers BULK COLLECT
        INTO l_rec_header_list LIMIT 1000;
  
      FORALL i IN l_rec_header_list.first .. l_rec_header_list.last
        INSERT INTO INF_GL_VOUCHER VALUES l_rec_header_list (i);
  
      -- 每1000条提交一次
      COMMIT;
  
      EXIT WHEN cur_receipt_headers%NOTFOUND;
    END LOOP;
    CLOSE cur_receipt_headers;
  
    -- 日志结束
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  EXCEPTION
    WHEN OTHERS THEN
      x_return_status := g_error;
      x_msg_data      := l_api_name || '->' || SQLERRM ||
                         dbms_utility.format_error_backtrace;
      log(x_msg_data);
  END sp_ar_receipt_headers;

  /**
  * @Author  : KALOSORA
  * @Purpose : 整理本地数据集，刷新 PENDING 的字段
  *
  * @Param   ：x_return_status 返回的参数状态
  * @Param   ：x_return_status 返回的消息
  * @Version : 1.0
  * @Since   : 2021/1/19 9:12:35
  */
  PROCEDURE update_local_data(x_return_status OUT NOCOPY VARCHAR2,
                              x_msg_count     OUT NOCOPY NUMBER,
                              x_msg_data      OUT NOCOPY VARCHAR2) IS
    l_api_name CONSTANT VARCHAR2(100) := 'update_local_data';
  
    -- 批量更新的数据结构
    TYPE record_update_type IS RECORD(
      id          inf_gl_voucheritemass.id%TYPE,
      vchrid      inf_gl_voucheritemass.vchrid%TYPE,
      unitcode    inf_gl_voucheritemass.unitcode%TYPE,
      vchrnum     inf_gl_voucheritemass.vchrnum%TYPE,
      MD_CUSTOMER inf_gl_voucheritemass.MD_CUSTOMER%TYPE,
      MD_CFITEM   inf_gl_voucheritemass.MD_CFITEM%TYPE,
      MD_INDUSTRY inf_gl_voucheritemass.md_industry%TYPE);
    TYPE record_update_tbl IS TABLE OF record_update_type;
    record_update record_update_tbl;
  
    -- 应付数据集
    CURSOR cur_ap_data IS
      SELECT igvm.id,
             igvm.vchrid,
             igv.unitcode,
             igv.vchrnum,
             igv.def_01 MD_CUSTOMER,
             igv.def_02 MD_CFITEM,
             DECODE((SELECT ffvs.FLEX_VALUE
                      FROM fnd_flex_values_vl ffvs, fnd_flex_value_sets ffv
                     WHERE 1 = 1
                       AND ffvs.FLEX_VALUE_SET_ID = ffv.FLEX_VALUE_SET_ID
                       AND ffv.FLEX_VALUE_SET_NAME =
                           'GJ_INTER_VIM_INDUSTRY'
                       AND ffvs.FLEX_VALUE = igvm.SUBJECTCODE
                       AND ffvs.ENABLED_FLAG = 'Y'
                       AND nvl(ffvs.END_DATE_ACTIVE, SYSDATE) >= SYSDATE),
                    NULL,
                    NULL,
                    (SELECT flv.DESCRIPTION
                       FROM fnd_lookup_values flv
                      WHERE 1 = 1
                        AND flv.LOOKUP_TYPE = 'GJ_INTER_LEGAL_TYPE'
                        AND flv.LANGUAGE = 'ZHS'
                        AND flv.ENABLED_FLAG = 'Y'
                        AND nvl(flv.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
                        AND flv.LOOKUP_CODE = igv.unitcode)) MD_INDUSTRY
        FROM inf_gl_voucher igv, inf_gl_voucheritemass igvm
       WHERE 1 = 1
         AND igv.id = igvm.vchrid
         AND igv.vchrtypecode IN ('3', '2')
         AND igvm.vchrnum = 'PENDING';
  
    -- 应收数据集
    CURSOR cur_ar_data IS
      SELECT igvm.id,
             igvm.vchrid,
             igv.unitcode,
             igv.vchrnum,
             igv.def_01 MD_CUSTOMER,
             igv.def_02 MD_CFITEM,
             DECODE((SELECT ffvs.FLEX_VALUE
                      FROM fnd_flex_values_vl ffvs, fnd_flex_value_sets ffv
                     WHERE 1 = 1
                       AND ffvs.FLEX_VALUE_SET_ID = ffv.FLEX_VALUE_SET_ID
                       AND ffv.FLEX_VALUE_SET_NAME =
                           'GJ_INTER_VIM_INDUSTRY'
                       AND ffvs.FLEX_VALUE = igvm.SUBJECTCODE
                       AND ffvs.ENABLED_FLAG = 'Y'
                       AND nvl(ffvs.END_DATE_ACTIVE, SYSDATE) >= SYSDATE),
                    NULL,
                    NULL,
                    (SELECT flv.DESCRIPTION
                       FROM fnd_lookup_values flv
                      WHERE 1 = 1
                        AND flv.LOOKUP_TYPE = 'GJ_INTER_LEGAL_TYPE'
                        AND flv.LANGUAGE = 'ZHS'
                        AND flv.ENABLED_FLAG = 'Y'
                        AND nvl(flv.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
                        AND flv.LOOKUP_CODE = igv.unitcode)) MD_INDUSTRY
        FROM inf_gl_voucher igv, inf_gl_voucheritemass igvm
       WHERE 1 = 1
         AND igv.id = igvm.vchrid
         AND igv.vchrtypecode IN ('4', '5')
         AND igvm.vchrnum = 'PENDING';
  
  BEGIN
    -- 日志开始
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  
    -- 更新应付数据集
    OPEN cur_ap_data;
    LOOP
      FETCH cur_ap_data BULK COLLECT
        INTO record_update LIMIT 1000;
  
      FORALL i IN 1 .. record_update.count
        UPDATE inf_gl_voucheritemass
           SET MD_CUSTOMER = NULL,
               MD_supplier = record_update(i).MD_CUSTOMER,
               MD_CFITEM   = record_update(i).MD_CFITEM,
               MD_INDUSTRY = record_update(i).MD_INDUSTRY,
               unitcode    = record_update(i).unitcode,
               vchrnum     = record_update(i).vchrnum
         WHERE 1 = 1
           AND id = record_update(i).id
           AND vchrid = record_update(i).vchrid;
  
      COMMIT;
  
      EXIT WHEN cur_ap_data%NOTFOUND;
    END LOOP;
    CLOSE cur_ap_data;
  
    -- 清空数据集
    record_update.delete;
  
    -- 更新应收数据集
    OPEN cur_ar_data;
    LOOP
      FETCH cur_ar_data BULK COLLECT
        INTO record_update LIMIT 1000;
  
      FORALL i IN 1 .. record_update.count
        UPDATE inf_gl_voucheritemass
           SET MD_supplier = NULL,
               MD_CUSTOMER = record_update(i).MD_CUSTOMER,
               MD_CFITEM   = record_update(i).MD_CFITEM,
               MD_INDUSTRY = record_update(i).MD_INDUSTRY,
               unitcode    = record_update(i).unitcode,
               vchrnum     = record_update(i).vchrnum
         WHERE 1 = 1
           AND id = record_update(i).id
           AND vchrid = record_update(i).vchrid;
  
      COMMIT;
  
      EXIT WHEN cur_ar_data%NOTFOUND;
    END LOOP;
    CLOSE cur_ar_data;
  
    -- 日志结束
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  
  EXCEPTION
    WHEN OTHERS THEN
      x_return_status := g_error;
      x_msg_data      := l_api_name || '->' || SQLERRM ||
                         dbms_utility.format_error_backtrace;
      log(x_msg_data);
  END update_local_data;

  /**
  * @Author  : KALOSORA
  * @Purpose : 将本地整理好的数据集插入到中间库
  *
  * @Param   ：x_return_status 返回的参数状态
  * @Param   ：x_return_status 返回的消息
  * @Version : 1.0
  * @Since   : 2021/1/19 9:12:35
  */
  PROCEDURE sp_remote_data(x_return_status OUT NOCOPY VARCHAR2,
                           x_msg_count     OUT NOCOPY NUMBER,
                           x_msg_data      OUT NOCOPY VARCHAR2) IS
    l_api_name CONSTANT VARCHAR2(100) := 'sp_remote_data';
  
  BEGIN
    -- 日志开始
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  
    -- 插入到凭证头表
    INSERT INTO INF_GL_VOUCHER@gj_congl_middb_prod
      SELECT * FROM INF_GL_VOUCHER;
  
    -- 插入到凭证行表
    INSERT INTO INF_GL_VOUCHERITEMASS@gj_congl_middb_prod
      SELECT * FROM INF_GL_VOUCHERITEMASS;
  
    -- 日志结束
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  
  EXCEPTION
    WHEN OTHERS THEN
      x_return_status := g_error;
      x_msg_data      := l_api_name || '->' || SQLERRM ||
                         dbms_utility.format_error_backtrace;
      log(x_msg_data);
  END sp_remote_data;

  /**
  * @Author  : KALOSORA
  * @Purpose : 全量同步，清空本地数据集和中间库
  *
  * @Param   ：x_return_status 返回的参数状态
  * @Param   ：x_return_status 返回的消息
  * @Version : 1.0
  * @Since   : 2021/1/19 9:12:35
  */
  PROCEDURE sp_sync_all(x_return_status OUT NOCOPY VARCHAR2,
                        x_msg_count     OUT NOCOPY NUMBER,
                        x_msg_data      OUT NOCOPY VARCHAR2) IS
    l_api_name CONSTANT VARCHAR2(100) := 'sp_remote_data';
  
  BEGIN
    -- 日志开始
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序开始';
    log(g_log_msg);
  
    -- 清空本地数据集
    DELETE FROM INF_GL_VOUCHER;
    DELETE FROM INF_GL_VOUCHERITEMASS;
    COMMIT;
  
    -- 清空中间库
    DELETE FROM INF_GL_VOUCHER@gj_congl_middb_prod;
    DELETE FROM INF_GL_VOUCHERITEMASS@gj_congl_middb_prod;
    COMMIT;
  
    -- 日志结束
    g_log_msg := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                 l_api_name || ' 程序结束';
    log(g_log_msg);
  
  EXCEPTION
    WHEN OTHERS THEN
      x_return_status := g_error;
      x_msg_data      := l_api_name || '->' || SQLERRM ||
                         dbms_utility.format_error_backtrace;
      log(x_msg_data);
  END sp_sync_all;

  /*==================================================
  Procedure Name :
      raise_exception
  Description:
      This procedure perform to 处理请求
  Argument:
      x_return_status             : Input Status : E(Error)/U(Unexception)/S(Success)
  History:
      1.00   2020-09-02  LIYIHUI   Creation
  ==================================================*/
  PROCEDURE process_request(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
                            p_commit        IN VARCHAR2 DEFAULT fnd_api.g_false,
                            x_return_status OUT NOCOPY VARCHAR2,
                            x_msg_count     OUT NOCOPY NUMBER,
                            x_msg_data      OUT NOCOPY VARCHAR2) IS
    l_api_name       CONSTANT VARCHAR2(30) := 'process_request';
    l_savepoint_name CONSTANT VARCHAR2(30) := 'sp_process_request';
  
  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 => x_return_status);
  
    -------------API body start
  
    -- 0.全量同步
    sp_sync_all(x_return_status => x_return_status,
                x_msg_count     => x_msg_count,
                x_msg_data      => x_msg_data);
    raise_exception(x_return_status);
  
    -- 1.凭证分录同步
    sp_vchrnum_lines(x_return_status => x_return_status,
                     x_msg_count     => x_msg_count,
                     x_msg_data      => x_msg_data);
    raise_exception(x_return_status);
  
    -- 2.应付发票凭证头同步
    sp_ap_invoice_headers(x_return_status => x_return_status,
                          x_msg_count     => x_msg_count,
                          x_msg_data      => x_msg_data);
    raise_exception(x_return_status);
  
    -- 3.应付付款凭证头同步
    sp_ap_payment_headers(x_return_status => x_return_status,
                          x_msg_count     => x_msg_count,
                          x_msg_data      => x_msg_data);
    raise_exception(x_return_status);
  
    -- 4.应收发票凭证头同步
    sp_ar_invoice_headers(x_return_status => x_return_status,
                          x_msg_count     => x_msg_count,
                          x_msg_data      => x_msg_data);
    raise_exception(x_return_status);
  
    -- 5.应收收款凭证头同步
    sp_ar_receipt_headers(x_return_status => x_return_status,
                          x_msg_count     => x_msg_count,
                          x_msg_data      => x_msg_data);
    raise_exception(x_return_status);
  
    -- 6.更新本地数据集
    update_local_data(x_return_status => x_return_status,
                      x_msg_count     => x_msg_count,
                      x_msg_data      => x_msg_data);
    raise_exception(x_return_status);
  
    -- 7.期初凭证同步
    sp_nc_voucher(x_return_status => x_return_status,
                  x_msg_count     => x_msg_count,
                  x_msg_data      => x_msg_data);
    raise_exception(x_return_status);
  
    -- 8.插入数据到中间库
    sp_remote_data(x_return_status => x_return_status,
                   x_msg_count     => x_msg_count,
                   x_msg_data      => x_msg_data);
    raise_exception(x_return_status);
  
    -------------API body end
  
    x_return_status := cux_api.end_activity(p_pkg_name  => g_pkg_name,
                                            p_api_name  => l_api_name,
                                            p_commit    => p_commit,
                                            x_msg_count => x_msg_count,
                                            x_msg_data  => x_msg_data);
  EXCEPTION
    WHEN fnd_api.g_exc_error THEN
      x_return_status := cux_api.handle_exceptions(p_pkg_name       => g_pkg_name,
                                                   p_api_name       => l_api_name,
                                                   p_savepoint_name => l_savepoint_name,
                                                   p_exc_name       => cux_api.g_exc_name_error,
                                                   x_msg_count      => x_msg_count,
                                                   x_msg_data       => x_msg_data);
    WHEN fnd_api.g_exc_unexpected_error THEN
      x_return_status := cux_api.handle_exceptions(p_pkg_name       => g_pkg_name,
                                                   p_api_name       => l_api_name,
                                                   p_savepoint_name => l_savepoint_name,
                                                   p_exc_name       => cux_api.g_exc_name_unexp,
                                                   x_msg_count      => x_msg_count,
                                                   x_msg_data       => x_msg_data);
    WHEN OTHERS THEN
      x_return_status := cux_api.handle_exceptions(p_pkg_name       => g_pkg_name,
                                                   p_api_name       => l_api_name,
                                                   p_savepoint_name => l_savepoint_name,
                                                   p_exc_name       => cux_api.g_exc_name_others,
                                                   x_msg_count      => x_msg_count,
                                                   x_msg_data       => x_msg_data);
  
  END process_request;

  /*==================================================
  Procedure Name :
      main
  Description:
      This procedure perform to 报表主函数
  Argument:
      errbuf               : concurrent return message buffer
      retcode              : concurrent return status code
                             0 success / 1 warning / 2 error
      p_book_id         : 账簿ID
      p_date_from       : 日期从
      p_date_to         : 至
      p_item_from       : 研发项目从
      p_item_to         : 至
  
  History:
      1.00   2020-09-02  LIYIHUI   Creation
  ==================================================*/
  PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT VARCHAR2) IS
    l_return_status VARCHAR2(1);
    l_msg_count     NUMBER;
    l_msg_data      VARCHAR2(4000);
  
  BEGIN
  
    --output header
    errbuf  := '0';
    retcode := NULL;
    cux_conc_utl.log_header;
  
    -- 处理请求
    process_request(p_init_msg_list => fnd_api.g_true,
                    p_commit        => fnd_api.g_false,
                    x_return_status => l_return_status,
                    x_msg_count     => l_msg_count,
                    x_msg_data      => l_msg_data);
    raise_exception(l_return_status);
  
    --output foot
    cux_conc_utl.log_footer;
  
  EXCEPTION
    WHEN fnd_api.g_exc_error THEN
      cux_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
      cux_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));
      cux_conc_utl.log_message_list;
      retcode := '2';
      errbuf  := SQLERRM;
  END main;
END gj_congl_voucher_sync_v2;

异常处理

查看异常

1.查询表

SELECT * FROM user_errors;

2.SQL * Plus 环境中

show errors;

查看警告信息

PL / SQL 警告的消息代码的格式为 PLW- nnnnn

警告信息等级

查询警告信息等级

可以通过 alter system 修改服务器参数文件

可以通过 alter session 修改当前会话

修改异常信息

启用部分

Alter session set plsql_warnings=’enable:performance’, ‘enable:severe’;

启用全部

Alter session set plsql_warnings=’enable:all’;

针对过程/函数启用编译警告

alter package gj_bc_vld_data_utl compile plsql_warnings = ‘enable:all’;

编辑警告

异常处理准则

1. 尽可能编写命名异常的异常处理程序，而不要使用 OTHERS 异常处理程序。
2. 对于每个异常处理程序，请仔细决定是让它提交事务，回滚它还是让它继续。
3. 每个 PL / SQL 程序的顶层都包含一个异常处理程序，避免未处理的异常。并且在子程序中显示抛出异常RAISE 或在 RAISE_APPLICATION_ERROR。

异常实例

1.自定义异常实例

CREATE PROCEDURE account_status (
  due_date DATE,
  today    DATE
) AUTHID DEFINER
IS
  past_due  EXCEPTION;  -- declare exception
BEGIN
  IF due_date < today THEN
    RAISE past_due;  -- explicitly raise exception
  END IF;
EXCEPTION
  WHEN past_due THEN  -- handle exception
    DBMS_OUTPUT.PUT_LINE ('Account past due.');
END;
/
 
BEGIN
  account_status (TO_DATE('01-JUL-2010', 'DD-MON-YYYY'),
                  TO_DATE('09-JUL-2010', 'DD-MON-YYYY'));
END;
/

2.使用RAISE_APPLICATION_ERROR自定义异常实例

CREATE PROCEDURE account_status (
  due_date DATE,
  today    DATE
) AUTHID DEFINER
IS
BEGIN
  IF due_date < today THEN                   -- explicitly raise exception
    RAISE_APPLICATION_ERROR(-20000, 'Account past due.');
  END IF;
END;
/
 
DECLARE
  past_due  EXCEPTION;                       -- declare exception
  PRAGMA EXCEPTION_INIT (past_due, -20000);  -- assign error code to exception
BEGIN
  account_status (TO_DATE('01-JUL-2010', 'DD-MON-YYYY'),
                  TO_DATE('09-JUL-2010', 'DD-MON-YYYY'));   -- invoke procedure

EXCEPTION
  WHEN past_due THEN                         -- handle exception
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
END;
/

实验：数据同步接口为例

package定义

CREATE OR REPLACE PACKAGE gj_inter_company_sync IS

  /**
  * @Author  : KALOSORA
  * @Purpose : 输出运行日志
  * @Param   : p_api_name 函数/过程名称
  * @Param   : p_api_name 日志等级 info/debug/error
  * @Param   : p_log_status 日志状态 start/end
  * @Param   : p_message 日志消息 
  * @Version : 1.0
  * @Since   : 2021/1/19 9:12:35 
  */
  PROCEDURE log(p_api_name   IN VARCHAR2,
                p_log_level  IN VARCHAR2,
                p_log_status IN VARCHAR2,
                p_message    IN VARCHAR2);

  /**
  * @Author  : KALOSORA
  * @Purpose : 自动根据快码GJ_INTER_COMPANY更新到客商弹性域中
  * @Param   ：x_return_status 返回的参数状态
  * @Param   ：x_return_status 返回的消息
  * @Version : 1.0
  * @Since   : 2021/1/19 9:12:35
  */
  PROCEDURE process_flex_sync(x_return_status OUT NOCOPY VARCHAR2,
                              x_return_msg    OUT NOCOPY VARCHAR2);

  /**
  * @Author  : KALOSORA
  * @Purpose : 程序入口
  * @Param   ：x_return_status 返回的参数状态
  * @Param   ：x_return_status 返回的消息
  * @Version : 1.0
  * @Since   : 2021/1/19 9:12:35
  */
  PROCEDURE main(x_return_status OUT NOCOPY VARCHAR2,
                 x_return_msg    OUT NOCOPY VARCHAR2);

END gj_inter_company_sync;

package内容

CREATE OR REPLACE PACKAGE BODY gj_inter_company_sync IS

  -- ==========================================================
  -- 日志参数
  g_conc_request_id NUMBER := fnd_global.conc_request_id;
  g_log_info        VARCHAR2(6) := 'INFO';
  g_log_error       VARCHAR2(6) := 'ERROR';
  g_log_start       VARCHAR2(6) := 'START';
  g_log_end         VARCHAR2(6) := 'END';

  -- 自定义应用异常
  APP_UNKNOWN_EXCEPTION EXCEPTION;
  PRAGMA EXCEPTION_INIT(APP_UNKNOWN_EXCEPTION, -20000);
  -- ==========================================================

  PROCEDURE log(p_api_name   IN VARCHAR2,
                p_log_level  IN VARCHAR2,
                p_log_status IN VARCHAR2,
                p_message    IN VARCHAR2) IS
  
  BEGIN
    IF g_conc_request_id > 0 THEN
      fnd_file.PUT_LINE(fnd_file.LOG,
                        '[' || p_api_name || '] ' || p_log_level || ' ' ||
                        to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                        p_log_status || ' - ' || p_message);
    ELSE
      dbms_output.put_line('[' || p_api_name || '] ' || p_log_level || ' ' ||
                           to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
                           p_log_status || ' - ' || p_message);
    END IF;
  END;

  PROCEDURE process_flex_sync(x_return_status OUT NOCOPY VARCHAR2,
                              x_return_msg    OUT NOCOPY VARCHAR2) IS
    l_api_name VARCHAR2(30) := upper('process_flex_sync');
  
    CURSOR cur_vendor IS
      SELECT flv.LOOKUP_CODE, company.id, company.inter_company
        FROM fnd_lookup_values flv,
             (SELECT pv.vendor_id   ID,
                     pv.vendor_name company_name,
                     pv.attribute2  inter_company
                FROM po_vendors pv
               WHERE 1 = 1
                 AND pv.attribute2 IS NULL) company
       WHERE 1 = 1
         AND company.company_name = flv.DESCRIPTION
         AND flv.LOOKUP_TYPE = 'GJ_INTER_COMPANY'
         AND flv.LANGUAGE = 'ZHS';
  
    CURSOR cur_customer IS
      SELECT flv.LOOKUP_CODE, company.id, company.inter_company
        FROM fnd_lookup_values flv,
             (SELECT hca.CUST_ACCOUNT_ID ID,
                     hp.party_name       company_name,
                     hca.ATTRIBUTE5      inter_company
                FROM hz_parties hp, hz_cust_accounts hca
               WHERE 1 = 1
                 AND hp.PARTY_ID = hca.PARTY_ID
                 AND hca.ATTRIBUTE5 IS NULL) company
       WHERE 1 = 1
         AND company.company_name = flv.DESCRIPTION
         AND flv.LOOKUP_TYPE = 'GJ_INTER_COMPANY'
         AND flv.LANGUAGE = 'ZHS';
  
  BEGIN
  
    log(l_api_name, g_log_info, g_log_start, NULL);
  
    -- 1. 更新供应商弹性域
    FOR rec_vendor IN cur_vendor LOOP
      UPDATE po_vendors
         SET attribute2 = rec_vendor.LOOKUP_CODE
       WHERE 1 = 1
         AND vendor_id = rec_vendor.id;
    END LOOP;
  
    -- 2. 更新客户弹性域
    FOR rec_customer IN cur_customer LOOP
      UPDATE hz_cust_accounts
         SET attribute5 = rec_customer.LOOKUP_CODE
       WHERE 1 = 1
         AND CUST_ACCOUNT_ID = rec_customer.id;
    END LOOP;
  
    log(l_api_name, g_log_info, g_log_end, NULL);
  
  EXCEPTION
    WHEN OTHERS THEN
      x_return_status := SQLCODE;
      x_return_msg    := SQLERRM || dbms_utility.format_error_backtrace;
      log(l_api_name, g_log_error, g_log_end, x_return_msg);
      RAISE APP_UNKNOWN_EXCEPTION;
  END;

  PROCEDURE main(x_return_status OUT NOCOPY VARCHAR2,
                 x_return_msg    OUT NOCOPY VARCHAR2) IS
    l_return_status VARCHAR2(10);
    l_return_msg    VARCHAR2(4000);
  BEGIN
    -- 日志开始
    cux_conc_utl.log_header;
  
    process_flex_sync(x_return_status => l_return_status,
                      x_return_msg    => l_return_msg);
  
    -- 日志结束
    cux_conc_utl.log_footer;
  END;

END gj_inter_company_sync;

4.异常事务处理

使用 SAVEPOINT start_transaction; 记录保存点

使用 rollback to [savepoint_name] 回滚保存点

【注意】在使用savepoint期间不能主动 commit ，否则保存点会消失

条件编译

可以根据不同的判断条件，编译/执行不同的程序

语法 $plsql标识符。注意$和标识符之间不能有空格

实例：输出plsql环境参数

BEGIN
  DBMS_OUTPUT.PUT_LINE('$$PLSCOPE_SETTINGS = '     || $$PLSCOPE_SETTINGS);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_CCFLAGS = '        || $$PLSQL_CCFLAGS);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_CODE_TYPE = '      || $$PLSQL_CODE_TYPE);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_WARNINGS = '       || $$PLSQL_WARNINGS);
  DBMS_OUTPUT.PUT_LINE('$$NLS_LENGTH_SEMANTICS = ' || $$NLS_LENGTH_SEMANTICS);
END;
/

实例2：根据数据库版本执行不同的指令

BEGIN
  $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN  -- selection directive begins
    $ERROR 'unsupported database release' $END  -- error directive
  $ELSE
    DBMS_OUTPUT.PUT_LINE (
      'Release ' || DBMS_DB_VERSION.VERSION || '.' ||
      DBMS_DB_VERSION.RELEASE || ' is supported.'
    );
  -- This COMMIT syntax is newly supported in 10.2:
  COMMIT WRITE IMMEDIATE NOWAIT;
  $END  -- selection directive ends
END;
/

其他的环境变量

可以通过 $$[参数名称] 代替在sql命令窗口中的 show parameter [参数名称]，以达到在plsql开发工具中查看服务器参数的效果

• $$PLSQL_LINE：获取当前package/function的行号
• $$PLSQL_UNIT：获取当前程序名称
• $$PLSQL_UNIT_OWNER：获取当前程序owner
• $$PLSQL_UNIT_TYPE：获取当前程序类型

正则表达式

用到的时候再来总结

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/regexp.html#GUID-1935FD80-A3CD-413F-BD2E-BBEFE64000B2

数据库锁

同上

闪回技术

Oracle Flashback

实例

查询实例：

SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';

数据恢复实例

INSERT INTO employees (
  SELECT * FROM employees
  AS OF TIMESTAMP
  TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
  WHERE last_name = 'Chung'
);

闪回事务

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/flashback.html#GUID-03D1CAAE-D940-444A-8771-B1BC636D105D

消息队列

Oracle AQ

‍

附录

用于管理PLSQL的表

编程规范

正确地使用数据类型

尽可能精确地指定数据类型

• 整数时指定NUMBER(n)，而不要使用NUMBER。
• VARCHAR2(n)的精度尽可能准确。

官方参考手册

1.SQL语言参考手册，即SQL Language Reference

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/dynamic-sql.html#GUID-7E2F596F-9CA3-4DC8-8333-0C117962DB73

‍