ITPub博客

首页 > Linux操作系统 > Linux操作系统 > R12 WEBADI 开发实例 (EBS 版本:12.1.3)

R12 WEBADI 开发实例 (EBS 版本:12.1.3)

原创 Linux操作系统 作者:asiaidea 时间:2011-07-22 11:11:53 0 删除 编辑

--Created by Tony Liu.

这两天做了一个R12 WEBADI的开发任务,任务描述如下:

1.  根据用户的data file, 导入数据至XLA, 生成Journals, 然后再调用标准请求,生成GL Journals

2.  建立staging table, 用来存放WEBADI导入的数据, 表名为c_xla_je_load_staging

3.  调用API 来生成XLA Journals

      xla_journal_entries_pub_pkg.create_journal_entry_header    生成Journal header

      xla_journal_entries_pub_pkg.create_journal_entry_line   生成Journal lines

4.  调用标准请求生成GL Journals

5.  建立Package :c_xla_je_load_pkg, 完成数据校验/数据导入到XLA/调用标准请求

6.  注册请求CUST XLA Journal Entry Load,调用上述package.

7.  在WEBADI 中调用上述请求.

如果上述工作在11i里做的话,估计得把我做吐血,因为11i的WEBADI还不是很完备,很多功能ORACLE没有提供开放的接口,只能用insert table来做。但是现在在R12中,做起来就简单多了。

R12 WEBADI比11i的强大了许多,反映在如下几方面:
1. 多了一个新的职责: Desktop integration manager, 在此职责下可完成所有的WEBADI的开发工作。
2. 现在可以在Excel 模板中定义Lov值验证
3. 增加了Interface 的定义,可以灵活定义Layout的数据源。
4. Layout中的定义多了一些选项,使得Layout的定义更加完善。
5. 增加了uploader的定义,可以灵活定义导入参数
6. 增加了importer的定义,可以灵活定义数据导入后的工作。
7. FNDLOAD 更加完善,可通过bneintegrator.lct 导出大多数的WEBADI定义。

所以这个Task 还真没花多少时间,就完成了。

---------------------------------以下是Table定义-------------------------------------
create table C_APPS.C_XLA_JE_LOAD_STAGING
(
  HEADER_ID                NUMBER not null,
  BALANCE_TYPE             VARCHAR2(1) not null,
  LEDGER_NAME              VARCHAR2(100) not null,
  LEDGER_ID                NUMBER,
  LEGAL_ENTITY_NAME        VARCHAR2(100),
  LEGAL_ENTITY_ID          NUMBER,
  REFERENCE_DATE           DATE,
  GL_DATE                  DATE not null,
  CATEGORY                 VARCHAR2(20) not null,
  H_DESCRIPTION            VARCHAR2(200) not null,
  LINE_NUMBER              NUMBER not null,
  ACCOUNTING_FF            VARCHAR2(100) not null,
  CODE_COMBINATION_ID      NUMBER,
  ACCOUNTING_CLASS         VARCHAR2(20) not null,
  ENTERED_CURRENCY         VARCHAR2(10) not null,
  ENTERED_DR               NUMBER,
  ENTERED_CR               NUMBER,
  THIRD_PARTY_TYPE         VARCHAR2(1),
  CUSTOMER_NUMBER          VARCHAR2(30),
  PARTY_ID                 NUMBER,
  CUSTOMER_SITE_NUMBER     VARCHAR2(30),
  PARTY_SITE_ID            NUMBER,
  L_DESCRIPTION            VARCHAR2(200),
  RECONCILIATION_REFERENCE VARCHAR2(200),
  PROCESS_FLAG             VARCHAR2(1),
  MESSAGE                  VARCHAR2(1000),
  GLB_QUANTITY             VARCHAR2(30),
  OID_AC1                  VARCHAR2(30),
  OID_AC2                  VARCHAR2(30),
  OID_AC3                  VARCHAR2(30),
  OID_AC4                  VARCHAR2(30),
  OID_AC5                  VARCHAR2(30),
  AE_HEADER_ID             NUMBER
)

---------------------------以下是Package定义------------------------------------

Process:  校验数据->调用API导入XLA->调用标准请求导入GL0->清空staging table
CREATE OR REPLACE PACKAGE BODY c_xla_je_load_pkg IS
  -- Author  : Tony Liu
 
  c_gl_application_id CONSTANT INTEGER := 101;
  c_ar_application_id CONSTANT INTEGER := 222;


  PROCEDURE validation(p_ledger_name       IN VARCHAR2,
                       p_legal_entity_name IN VARCHAR2,
                       p_reference_date    IN DATE,
                       p_gl_date           IN DATE,
                       p_category          IN VARCHAR2,
                       p_accounting_ff     IN VARCHAR2,
                       p_cust_num          IN VARCHAR2,
                       p_cust_site_num     IN VARCHAR2,
                       p_currency          IN VARCHAR2,
                       p_ledger_id         OUT NUMBER,
                       p_legal_entity_id   OUT NUMBER,
                       p_ccid              OUT NUMBER,
                       p_party_id          OUT NUMBER,
                       p_party_site_id     OUT NUMBER,
                       p_error_flag        OUT VARCHAR2,
                       p_error_msg         OUT VARCHAR2) IS
 
    vl_n_seg             NUMBER;
    vl_result            BOOLEAN;
    vl_currency          gl_currencies.currency_code%TYPE;
    vl_gd_closing_status gl_period_statuses.closing_status%TYPE;
    vl_rd_closing_status gl_period_statuses.closing_status%TYPE;
    vl_category          gl_je_categories.je_category_name%TYPE;
    vl_seg               fnd_flex_ext.segmentarray;
    vl_structure_number  NUMBER;
 
  BEGIN
    --Validate Ledger
    BEGIN
      SELECT ledger_id
        INTO p_ledger_id
        FROM gl_ledgers
       WHERE NAME = p_ledger_name;
    
    EXCEPTION
      WHEN no_data_found THEN
        p_ledger_id  := NULL;
        p_error_msg  := 'Invalid Ledger Name. ';
        p_error_flag := 'Y';
    END;
    --Validate Entity
    BEGIN
      IF (p_legal_entity_name IS NOT NULL) AND (p_ledger_id IS NOT NULL) THEN
        SELECT legal_entity_id
          INTO p_legal_entity_id
          FROM gl_ledger_le_v
         WHERE ledger_id = p_ledger_id
           AND legal_entity_name = p_legal_entity_name;
       
      END IF;
    EXCEPTION
      WHEN no_data_found THEN
        p_legal_entity_id := NULL;
        p_error_msg       := p_error_msg || 'Invalid Legal Entity Name. ';
        p_error_flag      := 'Y';
    END;
    --Validate GL Date
    BEGIN
      IF p_ledger_id IS NOT NULL THEN
        SELECT closing_status
          INTO vl_gd_closing_status
          FROM gl_period_statuses
         WHERE p_gl_date BETWEEN start_date AND end_date
           AND ledger_id = p_ledger_id
           AND application_id = c_gl_application_id;
        IF vl_gd_closing_status <> 'O' AND vl_gd_closing_status <> 'F' THEN
          p_error_msg  := p_error_msg ||
                          'GL Date is not in an open period. ';
          p_error_flag := 'Y';
        END IF;
      END IF;
    EXCEPTION
      WHEN no_data_found THEN
        p_error_msg  := 'Invalid GL Date.';
        p_error_flag := 'Y';
    END;
    --Validate Reference Date
    IF p_reference_date IS NOT NULL THEN
      BEGIN
        IF p_ledger_id IS NOT NULL THEN
          SELECT closing_status
            INTO vl_rd_closing_status
            FROM gl_period_statuses
           WHERE p_reference_date BETWEEN start_date AND end_date
             AND ledger_id = p_ledger_id
             AND application_id = c_gl_application_id;
          IF vl_rd_closing_status <> 'O' AND vl_rd_closing_status <> 'F' THEN
            p_error_msg  := p_error_msg ||
                            'Reference Date is not in an open period. ';
            p_error_flag := 'Y';
          END IF;
        END IF;
      EXCEPTION
        WHEN no_data_found THEN
          p_error_msg  := 'Invalid GL Date.';
          p_error_flag := 'Y';
      END;
    END IF;
    --Validate Category Name
    BEGIN
      SELECT je_category_name
        INTO vl_category
        FROM gl_je_categories
       WHERE je_category_name = p_category;
    EXCEPTION
      WHEN no_data_found THEN
        p_error_msg  := p_error_msg || 'Invalid JE Category. ';
        p_error_flag := 'Y';
    END;
    --Validate Code Combinations
    BEGIN
      SELECT chart_of_accounts_id
        INTO vl_structure_number
        FROM gl_ledgers
       WHERE ledger_id = p_ledger_id;
      vl_n_seg := fnd_flex_ext.breakup_segments(p_accounting_ff,
                                                '-',
                                                vl_seg);
   
      vl_result := fnd_flex_ext.get_combination_id(application_short_name => 'SQLGL',
                                                   key_flex_code          => 'GL#',
                                                   structure_number       => vl_structure_number,
                                                   validation_date        => SYSDATE,
                                                   n_segments             => vl_n_seg,
                                                   segments               => vl_seg,
                                                   combination_id         => p_ccid);
      IF vl_result = FALSE THEN
        p_error_msg  := p_error_msg || 'Invalid Accounting Flexfield. ';
        p_error_flag := 'Y';
      END IF;
    EXCEPTION
      WHEN no_data_found THEN
        vl_structure_number := 0;
        p_error_msg         := p_error_msg || 'Invalid Ledger. ';
        p_error_flag        := 'Y';
    END;
    --Validate Customer
    IF p_cust_num IS NOT NULL THEN
      BEGIN
        SELECT hca.cust_account_id party_id,
               MIN(hcsu.site_use_id) site_use_id
          INTO p_party_id, p_party_site_id
          FROM hz_cust_site_uses_all  hcsu,
               hz_cust_accounts       hca,
               hz_cust_acct_sites_all hcas,
               hz_parties             hp,
               hz_party_sites         hps
         WHERE hp.party_id = hca.party_id
           AND hps.party_site_id = hcas.party_site_id
           AND hca.cust_account_id = hcas.cust_account_id
           AND hcas.status = 'A'
           AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
           AND hca.account_number = p_cust_num
           AND hps.party_site_number = p_cust_site_num
         GROUP BY hca.cust_account_id;
      EXCEPTION
        WHEN no_data_found THEN
          p_party_site_id := NULL;
          p_error_msg     := p_error_msg ||
                             'Invalid Customer or Customer Site Number. ';
          p_error_flag    := 'Y';
      END;
    END IF;
    --Validate Currency
    BEGIN
      SELECT currency_code
        INTO vl_currency
        FROM gl_currencies
       WHERE currency_code = p_currency
         AND enabled_flag = 'Y'
         AND nvl(end_date_active, SYSDATE) >= SYSDATE;
    EXCEPTION
      WHEN no_data_found THEN
        p_error_msg  := p_error_msg || 'Invalid Currency Code. ';
        p_error_flag := 'Y';
    END;
 
  END validation;

  PROCEDURE main(po_errbuf OUT VARCHAR2, po_retcode OUT VARCHAR2) IS
    vc_api_version            CONSTANT NUMBER := 1.0;
    vc_balance_type_code      CONSTANT xla_ae_headers.balance_type_code%TYPE := 'A';
    vc_init_msg_list          CONSTANT VARCHAR2(1) := fnd_api.g_true;
    vc_gl_transfer_mode       CONSTANT VARCHAR(1) := 'S';
    vc_party_type_code        CONSTANT VARCHAR(1) := 'C';
    vc_sla_je_complete_option CONSTANT VARCHAR2(1) := 'F';
    vc_retcode_normal         CONSTANT NUMBER := 0;
    vc_retcode_warning        CONSTANT NUMBER := 1;
    vc_retcode_error          CONSTANT NUMBER := 2;
    vc_support_ref1           CONSTANT VARCHAR2(15) := 'C_GBL_QUANTITY';
    vc_support_ref2           CONSTANT VARCHAR2(15) := 'C_OID';
    vc_period_set             CONSTANT VARCHAR2(11) := 'MM CALENDAR';
    vc_primary_ledger         CONSTANT VARCHAR2(7) := 'PRIMARY';
 
    CURSOR c_data IS
      SELECT * FROM c_apps.c_xla_je_load_staging ORDER BY header_id;
 
    CURSOR c_error_data IS
      SELECT *
        FROM c_apps.c_xla_je_load_staging
       WHERE process_flag = 'E'
       ORDER BY header_id;
 
    CURSOR c_valid_data_h IS
      SELECT DISTINCT header_id
        FROM c_apps.c_xla_je_load_staging
       ORDER BY header_id;
 
    CURSOR c_valid_data_l(p_header_id NUMBER) IS
      SELECT *
        FROM c_apps.c_xla_je_load_staging
       WHERE header_id = p_header_id;
 
    CURSOR c_period_name IS
      SELECT DISTINCT gp.period_name
        FROM gl_periods gp, c_xla_je_load_staging cxjls
       WHERE cxjls.gl_date BETWEEN gp.start_date AND gp.end_date
         AND gp.period_set_name = vc_period_set;
 
    --For "Transfer Journal Entries to GL"   
    CURSOR c_ledger IS
      SELECT DISTINCT cxjls.ledger_id, cxjls.ledger_name
        FROM c_apps.c_xla_je_load_staging cxjls, gl.gl_ledgers gls
       WHERE cxjls.ledger_id = gls.ledger_id
         AND gls.ledger_category_code = vc_primary_ledger;
 
    vl_error_flag       VARCHAR2(1) := NULL;
    vl_error_msg        VARCHAR2(1000) := NULL;
    vl_ledger_id        NUMBER := 0;
    vl_legal_entity_id  NUMBER := 0;
    vl_ccid             NUMBER := 0;
    vl_party_id         NUMBER := 0;
    vl_party_site_id    NUMBER := 0;
    vl_count_error      NUMBER := 0;
    vl_tol_dr           NUMBER := 0;
    vl_tol_cr           NUMBER := 0;
    pi_ledger_id        gl_ledgers.ledger_id%TYPE;
    pi_legal_entity_id  NUMBER;
    pi_reference_date   DATE;
    pi_gl_date          DATE;
    pi_category         gl_je_categories.je_category_name%TYPE;
    pi_description      xla_ae_headers.description%TYPE;
    vl_return_status    VARCHAR2(5);
    vl_msg_count        NUMBER;
    vl_msg_data         VARCHAR2(4000);
    vl_l_return_status  VARCHAR2(5);
    vl_l_msg_count      NUMBER;
    vl_l_msg_data       VARCHAR2(4000);
    vl_ae_header_id     xla_ae_headers.ae_header_id%TYPE;
    vl_event_id         xla_ae_headers.event_id%TYPE;
    vl_ae_line_num      xla_ae_lines.ae_line_num%TYPE;
    vl_c_return_status  VARCHAR2(5);
    vl_c_msg_count      NUMBER;
    vl_c_msg_data       VARCHAR2(4000);
    vl_complete_retcode VARCHAR2(5);
    po_ret_code         NUMBER;
    vl_i                NUMBER;
    vl_err_msg          VARCHAR2(2000);
    vl_request_id       NUMBER;
    vl_gl_date          VARCHAR2(20);
 
  BEGIN
 
    --Call Validation process to validate each lines
    fnd_file.put_line(fnd_file.log, 'Start validation...');
 
    FOR c1 IN c_data LOOP
      fnd_file.put_line(fnd_file.log,
                        'Validating...' || c1.header_id || '-' ||
                        c1.line_number);
      validation(c1.ledger_name,
                 c1.legal_entity_name,
                 c1.gl_date,
                 c1.reference_date,
                 c1.category,
                 c1.accounting_ff,
                 c1.customer_number,
                 c1.customer_site_number,
                 c1.entered_currency,
                 vl_ledger_id,
                 vl_legal_entity_id,
                 vl_ccid,
                 vl_party_id,
                 vl_party_site_id,
                 vl_error_flag,
                 vl_error_msg);
      UPDATE c_apps.c_xla_je_load_staging cxjs
         SET ledger_id           = vl_ledger_id,
             legal_entity_id     = vl_legal_entity_id,
             code_combination_id = vl_ccid,
             party_id            = vl_party_id,
             party_site_id       = vl_party_site_id,
             process_flag        = decode(vl_error_flag, 'Y', 'E', 'N'),
             message             = vl_error_msg
       WHERE cxjs.header_id = c1.header_id
         AND cxjs.line_number = c1.line_number;
    END LOOP;
    COMMIT;
    --Validate balance
    fnd_file.put_line(fnd_file.log, 'Validating balance ');
    FOR c_tol IN c_valid_data_h LOOP
      SELECT SUM(nvl(entered_dr, 0)), SUM(nvl(entered_cr, 0))
        INTO vl_tol_dr, vl_tol_cr
        FROM c_apps.c_xla_je_load_staging
       WHERE header_id = c_tol.header_id;
      IF vl_tol_dr <> vl_tol_cr THEN
        UPDATE c_apps.c_xla_je_load_staging
           SET process_flag = 'E',
               message      = message || 'Unbalanced Journal.'
         WHERE header_id = c_tol.header_id;
      END IF;
    END LOOP;
    COMMIT;
    fnd_file.put_line(fnd_file.log, 'Validation completed. ');
    SELECT COUNT(1)
      INTO vl_count_error
      FROM c_apps.c_xla_je_load_staging
     WHERE process_flag = 'E';
    IF vl_count_error = 0 THEN
      fnd_file.put_line(fnd_file.log, 'Starting API ');
      --Call API load Header
      FOR c3 IN c_valid_data_h LOOP
        SELECT ledger_id,
               legal_entity_id,
               reference_date,
               gl_date,
               category,
               h_description
          INTO pi_ledger_id,
               pi_legal_entity_id,
               pi_reference_date,
               pi_gl_date,
               pi_category,
               pi_description
          FROM c_apps.c_xla_je_load_staging
         WHERE header_id = c3.header_id
           AND rownum = 1;
        xla_journal_entries_pub_pkg.create_journal_entry_header(p_api_version            => vc_api_version,
                                                                p_init_msg_list          => vc_init_msg_list,
                                                                p_application_id         => c_ar_application_id,
                                                                p_ledger_id              => pi_ledger_id,
                                                                p_legal_entity_id        => pi_legal_entity_id,
                                                                p_gl_date                => pi_gl_date,
                                                                p_description            => pi_description,
                                                                p_je_category_name       => pi_category,
                                                                p_balance_type_code      => vc_balance_type_code,
                                                                p_budget_version_id      => NULL,
                                                                p_reference_date         => pi_reference_date,
                                                                p_budgetary_control_flag => NULL,
                                                                p_attribute_category     => NULL,
                                                                p_attribute1             => NULL,
                                                                p_attribute2             => NULL,
                                                                p_attribute3             => NULL,
                                                                p_attribute4             => NULL,
                                                                p_attribute5             => NULL,
                                                                p_attribute6             => NULL,
                                                                p_attribute7             => NULL,
                                                                p_attribute8             => NULL,
                                                                p_attribute9             => NULL,
                                                                p_attribute10            => NULL,
                                                                p_attribute11            => NULL,
                                                                p_attribute12            => NULL,
                                                                p_attribute13            => NULL,
                                                                p_attribute14            => NULL,
                                                                p_attribute15            => NULL,
                                                                x_return_status          => vl_return_status,
                                                                x_msg_count              => vl_msg_count,
                                                                x_msg_data               => vl_msg_data,
                                                                x_ae_header_id           => vl_ae_header_id,
                                                                x_event_id               => vl_event_id);
        IF vl_return_status = fnd_api.g_ret_sts_success THEN
          --Call API load lines
          FOR c4 IN c_valid_data_l(c3.header_id) LOOP
            xla_journal_entries_pub_pkg.create_journal_entry_line(p_api_version           => vc_api_version,
                                                                  p_init_msg_list         => vc_init_msg_list,
                                                                  p_application_id        => c_ar_application_id,
                                                                  p_ae_header_id          => vl_ae_header_id,
                                                                  p_displayed_line_number => c4.line_number,
                                                                  p_code_combination_id   => c4.code_combination_id,
                                                                  p_gl_transfer_mode      => vc_gl_transfer_mode,
                                                                  p_accounting_class_code => c4.accounting_class,
                                                                  p_currency_code         => c4.entered_currency,
                                                                  p_entered_dr            => c4.entered_dr,
                                                                  p_entered_cr            => c4.entered_cr,
                                                                  p_accounted_dr          => NULL,
                                                                  p_accounted_cr          => NULL,
                                                                  p_conversion_type       => 'Corporate',
                                                                  p_conversion_date       => pi_gl_date,
                                                                  p_conversion_rate       => NULL,
                                                                  p_party_type_code       => vc_party_type_code,
                                                                  p_party_id              => c4.party_id,
                                                                  p_party_site_id         => c4.party_site_id,
                                                                  p_description           => c4.l_description,
                                                                  p_statistical_amount    => NULL,
                                                                  p_jgzz_recon_ref        => NULL,
                                                                  p_attribute_category    => NULL,
                                                                  p_encumbrance_type_id   => NULL,
                                                                  p_attribute1            => NULL,
                                                                  p_attribute2            => NULL,
                                                                  p_attribute3            => NULL,
                                                                  p_attribute4            => NULL,
                                                                  p_attribute5            => NULL,
                                                                  p_attribute6            => NULL,
                                                                  p_attribute7            => NULL,
                                                                  p_attribute8            => NULL,
                                                                  p_attribute9            => NULL,
                                                                  p_attribute10           => NULL,
                                                                  p_attribute11           => NULL,
                                                                  p_attribute12           => NULL,
                                                                  p_attribute13           => NULL,
                                                                  p_attribute14           => NULL,
                                                                  p_attribute15           => NULL,
                                                                  x_return_status         => vl_l_return_status,
                                                                  x_msg_count             => vl_l_msg_count,
                                                                  x_msg_data              => vl_l_msg_data,
                                                                  x_ae_line_num           => vl_ae_line_num);
            IF vl_l_return_status <> fnd_api.g_ret_sts_success THEN
              ROLLBACK;
              fnd_file.put_line(fnd_file.log,
                                'Record Header ' || c3.header_id ||
                                ' failed!');
              fnd_file.put_line(fnd_file.log,
                                'Error occurred in create_journal_entry_line API');
              po_retcode := vc_retcode_error;
              IF (nvl(vl_l_msg_count, 0) = 0) THEN
                fnd_file.put_line(fnd_file.log, 'No message return');
              ELSE
                FOR vl_i IN 1 .. vl_l_msg_count LOOP
                  vl_err_msg := fnd_msg_pub.get(vl_i, 'F');
                  fnd_file.put_line(fnd_file.log,
                                    substr(vl_err_msg, 1, 2000));
                END LOOP;
              END IF;
              EXIT;
            END IF;
            --Update Supporting Reference
            IF (c4.glb_quantity IS NOT NULL) OR (c4.oid_ac1 IS NOT NULL) OR
               (c4.oid_ac2 IS NOT NULL) OR (c4.oid_ac3 IS NOT NULL) OR
               (c4.oid_ac4 IS NOT NULL) OR (c4.oid_ac5 IS NOT NULL) THEN
              UPDATE xla_ae_lines
                 SET analytical_balance_flag = 'Y'
               WHERE ae_line_num = vl_ae_line_num
                 AND ae_header_id = vl_ae_header_id;
              IF c4.glb_quantity IS NOT NULL THEN
                INSERT INTO xla_ae_line_acs
                VALUES
                  (vl_ae_header_id,
                   vl_ae_line_num,
                   vc_support_ref1,
                   'C',
                   'DEFAULT',
                   1,
                   c4.glb_quantity,
                   NULL,
                   NULL,
                   NULL,
                   NULL);
                IF (c4.oid_ac1 IS NOT NULL) OR (c4.oid_ac2 IS NOT NULL) OR
                   (c4.oid_ac3 IS NOT NULL) OR (c4.oid_ac4 IS NOT NULL) OR
                   (c4.oid_ac5 IS NOT NULL) THEN
                  INSERT INTO xla_ae_line_acs
                  VALUES
                    (vl_ae_header_id,
                     vl_ae_line_num,
                     vc_support_ref2,
                     'C',
                     'DEFAULT',
                     1,
                     c4.oid_ac1,
                     c4.oid_ac2,
                     c4.oid_ac3,
                     c4.oid_ac4,
                     c4.oid_ac5);
                END IF;
              ELSE
                INSERT INTO xla_ae_line_acs
                VALUES
                  (vl_ae_header_id,
                   vl_ae_line_num,
                   vc_support_ref2,
                   'C',
                   'DEFAULT',
                   1,
                   c4.oid_ac1,
                   c4.oid_ac2,
                   c4.oid_ac3,
                   c4.oid_ac4,
                   c4.oid_ac5);
              END IF;
            END IF;
         
          END LOOP;
          --Call API complete the journals
          xla_journal_entries_pub_pkg.complete_journal_entry(p_api_version        => vc_api_version,
                                                             p_init_msg_list      => vc_init_msg_list,
                                                             p_application_id     => c_ar_application_id,
                                                             p_ae_header_id       => vl_ae_header_id,
                                                             p_completion_option  => vc_sla_je_complete_option,
                                                             x_return_status      => vl_c_return_status,
                                                             x_msg_count          => vl_c_msg_count,
                                                             x_msg_data           => vl_c_msg_data,
                                                             x_completion_retcode => vl_complete_retcode);
          IF vl_c_return_status = fnd_api.g_ret_sts_success THEN
            COMMIT;
            UPDATE c_apps.c_xla_je_load_staging
               SET process_flag = 'P', ae_header_id = vl_ae_header_id --Changed on Jul 22
             WHERE header_id = c3.header_id;
            COMMIT;
            fnd_file.put_line(fnd_file.log,
                              'Record Header ' || c3.header_id ||
                              ' was processed successfully.');
          ELSE
            ROLLBACK;
            fnd_file.put_line(fnd_file.log,
                              'Record Header ' || c3.header_id ||
                              ' failed!');
            fnd_file.put_line(fnd_file.log,
                              'Error occurred in complete_journal_entry API');
            po_retcode := vc_retcode_error;
            IF (nvl(vl_c_msg_count, 0) = 0) THEN
              fnd_file.put_line(fnd_file.log, 'No message return');
            ELSE
              FOR vl_i IN 1 .. vl_c_msg_count LOOP
                vl_err_msg := fnd_msg_pub.get(vl_i, 'F');
                fnd_file.put_line(fnd_file.log,
                                  substr(vl_err_msg, 1, 2000));
              END LOOP;
            END IF;
         
          END IF;
        ELSE
          ROLLBACK;
          fnd_file.put_line(fnd_file.log,
                            'Record Header ' || c3.header_id || ' failed!');
          fnd_file.put_line(fnd_file.log,
                            'Error occurred in create_journal_entry_header API');
          po_retcode := vc_retcode_error;
          IF (nvl(vl_msg_count, 0) = 0) THEN
            fnd_file.put_line(fnd_file.log, 'No message return');
          ELSE
            FOR vl_i IN 1 .. vl_msg_count LOOP
              vl_err_msg := fnd_msg_pub.get(vl_i, 'F');
              fnd_file.put_line(fnd_file.log, substr(vl_err_msg, 1, 2000));
            END LOOP;
          END IF;
       
        END IF;
      END LOOP;
      IF po_retcode = vc_retcode_error THEN
        fnd_file.put_line(fnd_file.log,
                          'The file was processed with failed records, please check! ');
      ELSE
        fnd_file.put_line(fnd_file.log,
                          'The file was processed successfully. ');
     
        --Run report; changed on Jul 22
        FOR rec_period IN c_period_name LOOP
          sla_report(rec_period.period_name);
        END LOOP;
        --Submit "Transfer Journal Entries to GL"                             
        apps.fnd_global.apps_initialize(user_id      => apps.fnd_global.user_id,
                                        resp_id      => apps.fnd_global.resp_id,
                                        resp_appl_id => apps.fnd_global.resp_appl_id);
        FOR rec_ledger IN c_ledger LOOP
       
          SELECT to_char(MAX(gl_date), 'yyyy/mm/dd hh24:mi:ss')
            INTO vl_gl_date
            FROM c_apps.c_xla_je_load_staging
           WHERE ledger_id = rec_ledger.ledger_id;
       
          vl_request_id := fnd_request.submit_request(application => 'XLA',
                                                      program     => 'XLAGLTRN',
                                                      description => NULL,
                                                      start_time  => NULL, -- To start immediately
                                                      sub_request => FALSE,
                                                      argument1   => 222,
                                                      argument2   => 222,
                                                      argument3   => 'Y',
                                                      argument4   => rec_ledger.ledger_id,
                                                      argument5   => 'MANUAL',
                                                      argument6   => vl_gl_date,
                                                      argument7   => 'N',
                                                      argument8   => '',
                                                      argument9   => '',
                                                      argument10  => 'Y',
                                                      argument11  => '',
                                                      argument12  => '',
                                                      argument13  => 'Y',
                                                      argument14  => 'Y',
                                                      argument15  => 'N',
                                                      argument16  => '',
                                                      argument17  => '',
                                                      argument18  => '',
                                                      argument19  => '',
                                                      argument20  => 'Receivables',
                                                      argument21  => 'Receivables',
                                                      argument22  => rec_ledger.ledger_name,
                                                      argument23  => 'Manual',
                                                      argument24  => 'No',
                                                      argument25  => '',
                                                      argument26  => '',
                                                      argument27  => '',
                                                      argument28  => 'Yes',
                                                      argument29  => 'No',
                                                      argument30  => '',
                                                      argument31  => '',
                                                      argument32  => '',
                                                      argument33  => '',
                                                      argument34  => '',
                                                      argument35  => '',
                                                      argument36  => '',
                                                      argument37  => '',
                                                      argument38  => 'N',
                                                      argument39  => chr(0) -- end with chr(0)as end of parameters
                                                      );
          fnd_file.put_line(fnd_file.log,
                            '''Transfer Journal Entries to GL''' ||
                            ' Request:' || vl_request_id ||
                            ' submitted for Ledger ' ||
                            rec_ledger.ledger_name);
        END LOOP;
     
      END IF;
    ELSE
      po_retcode := vc_retcode_warning;
      fnd_file.put_line(fnd_file.log, 'Invalid data, please correct!');
      fnd_file.put_line(fnd_file.log,
                        'Header Identifier, ' || 'Ledger Name, ' ||
                        'Legal Entity Name, ' || 'GL Date, ' ||
                        'Category, ' || 'Line number, ' ||
                        'Accounting Flexfield, ' || 'Accounnting Class, ' ||
                        'Customer Number, ' || 'Customer Site Number, ' ||
                        'Entered Currency, ' || 'Error Messsage');
      FOR c2 IN c_error_data LOOP
        fnd_file.put_line(fnd_file.log,
                          c2.header_id || ',' || c2.ledger_name || ',' ||
                          c2.legal_entity_name || ',' || c2.gl_date || ',' ||
                          c2.category || ',' || c2.line_number || ',' ||
                          c2.accounting_ff || ',' || c2.accounting_class || ',' ||
                          c2.customer_number || ',' ||
                          c2.customer_site_number || ',' ||
                          c2.entered_currency || ',' || c2.message);
      END LOOP;
   
    END IF;
 
    --delete all data from staging table
    BEGIN
      fnd_file.put_line(fnd_file.log,
                        'Delete all data from staging table!');
      DELETE FROM c_apps.c_xla_je_load_staging;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        fnd_file.put_line(fnd_file.log,
                          'Failed to delete data from staging table!');
        po_retcode := vc_retcode_warning;
    END;
 
  END main;

  PROCEDURE sla_report(pi_period_name IN VARCHAR2) IS
    vl_quantity      VARCHAR2(30);
    vl_discount_type VARCHAR2(30);
    vl_prod_code     VARCHAR2(30);
    vl_deal_num      VARCHAR2(30);
    vl_units         VARCHAR2(30);
    vl_comments      VARCHAR2(30);
 
    CURSOR c_staging IS
      SELECT DISTINCT header_id, ae_header_id
        FROM c_xla_je_load_staging
       ORDER BY header_id;
 
    CURSOR c_xla_je(pi_ae_header_id IN NUMBER) IS
      SELECT xah.ae_header_id,
             gl.name,
             xah.je_category_name,
             xah.accounting_date,
             xah.reference_date,
             xah.description description,
             xal.ae_line_num,
             gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
             gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
             gcc.segment7 code_combination,
             xal.accounting_class_code,
             xal.currency_code,
             xal.entered_dr,
             xal.accounted_dr,
             xal.entered_cr,
             xal.accounted_cr,
             xal.description line_description,
             hp.party_name customer_name,
             hca.account_number customer_number,
             hps.party_site_number site_number
        FROM apps.xla_ae_headers         xah,
             apps.xla_ae_lines           xal,
             apps.gl_code_combinations   gcc,
             apps.gl_ledgers             gl,
             apps.hz_cust_accounts       hca,
             apps.hz_cust_acct_sites_all hcas,
             apps.hz_parties             hp,
             apps.hz_party_sites         hps,
             apps.hz_cust_site_uses_all  hcsu
       WHERE xah.ledger_id = gl.ledger_id
         AND xah.ae_header_id = xal.ae_header_id
         AND xal.code_combination_id = gcc.code_combination_id
         AND xal.party_id = hca.cust_account_id
         AND xal.party_site_id = hcsu.site_use_id
         AND hp.party_id = hca.party_id
         AND hps.party_site_id = hcas.party_site_id
         AND hca.cust_account_id = hcas.cust_account_id
         AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
         AND hcas.status = 'A'
         AND xah.event_type_code = 'MANUAL'
         AND xah.je_category_name = 'Discounts'
         AND xah.ae_header_id = pi_ae_header_id;
 
    CURSOR c_xla_ac(pi_ae_header_id IN NUMBER, pi_ae_line_num IN NUMBER) IS
      SELECT xala.analytical_criterion_code,
             xala.ac1,
             xala.ac2,
             xala.ac3,
             xala.ac4,
             xala.ac5
        FROM xla_ae_line_acs xala
       WHERE xala.ae_header_id = pi_ae_header_id
         AND xala.ae_line_num = pi_ae_line_num;
 
    CURSOR c_xla_je_period IS
      SELECT ae_header_id
        FROM xla_ae_headers
       WHERE event_type_code = 'MANUAL'
         AND je_category_name = 'Discounts'
       ORDER BY ae_header_id;
 
  BEGIN
    fnd_file.put_line(fnd_file.log,
                      'Run report for period: ' || pi_period_name);
    --Generate report for the file loaded                     
    fnd_file.put_line(fnd_file.output, 'SLA Journals Creation Report');
    fnd_file.put_line(fnd_file.output,
                      'AE_HEADER_ID| LEDGER_NAME|  JE_CATEGORY_NAME|  ACCOUNTING_DATE|  REFERENCE_DATE|  DESCRIPTION|  AE_LINE_NUM|  ACCOUNTING_FF|  ACCOUNTING_CLASS_CODE|  CURRENCY_CODE|  ENTERED_DR|  ACCOUNTED_DR|  ENTERED_CR|  ACCOUNTED_CR|  LINE_DESCRIPTION|  CUSTOMER_NAME|  CUSTOMER_NUMBER|  SITE_NUMBER|  GBL_QUANTITY| DISCOUNT_TYPE| PRODUCT_CODE| DEAL_NUMBER| UNITS| COMMENTS');
    FOR rec_staging IN c_staging LOOP
      FOR rec_xla_je IN c_xla_je(rec_staging.ae_header_id) LOOP
        vl_quantity      := NULL;
        vl_discount_type := NULL;
        vl_prod_code     := NULL;
        vl_deal_num      := NULL;
        vl_units         := NULL;
        vl_comments      := NULL;
        FOR rec_xla_ac IN c_xla_ac(rec_xla_je.ae_header_id,
                                   rec_xla_je.ae_line_num) LOOP
          IF rec_xla_ac.analytical_criterion_code = 'C_GBL_QUANTITY' THEN
            vl_quantity := rec_xla_ac.ac1;
          ELSIF rec_xla_ac.analytical_criterion_code = 'C_OID' THEN
            vl_discount_type := rec_xla_ac.ac1;
            vl_prod_code     := rec_xla_ac.ac2;
            vl_deal_num      := rec_xla_ac.ac3;
            vl_units         := rec_xla_ac.ac4;
            vl_comments      := rec_xla_ac.ac5;
          END IF;
        END LOOP;
        fnd_file.put_line(fnd_file.output,
                          rec_xla_je.ae_header_id || '|' || rec_xla_je.name || '|' ||
                          rec_xla_je.je_category_name || '|' ||
                          rec_xla_je.accounting_date || '|' ||
                          rec_xla_je.reference_date || '|' ||
                          rec_xla_je.description || '|' ||
                          rec_xla_je.ae_line_num || '|' ||
                          rec_xla_je.code_combination || '|' ||
                          rec_xla_je.accounting_class_code || '|' ||
                          rec_xla_je.currency_code || '|' ||
                          rec_xla_je.entered_dr || '|' ||
                          rec_xla_je.accounted_dr || '|' ||
                          rec_xla_je.entered_cr || '|' ||
                          rec_xla_je.accounted_cr || '|' ||
                          rec_xla_je.line_description || '|' ||
                          rec_xla_je.customer_name || '|' ||
                          rec_xla_je.customer_number || '|' ||
                          rec_xla_je.site_number || '|' || vl_quantity || '|' ||
                          vl_discount_type || '|' || vl_prod_code || '|' ||
                          vl_deal_num || '|' || vl_units || '|' ||
                          vl_comments);
     
      END LOOP;
    END LOOP;
    fnd_file.put_line(fnd_file.output, '         ');
    fnd_file.put_line(fnd_file.output, '         ');
    fnd_file.put_line(fnd_file.output,
                      'SLA Journals Creation Report for Period: ' ||
                      pi_period_name);
    fnd_file.put_line(fnd_file.output,
                      'AE_HEADER_ID| LEDGER_NAME|  JE_CATEGORY_NAME|  ACCOUNTING_DATE|  REFERENCE_DATE|  DESCRIPTION|  AE_LINE_NUM|  ACCOUNTING_FF|  ACCOUNTING_CLASS_CODE|  CURRENCY_CODE|  ENTERED_DR|  ACCOUNTED_DR|  ENTERED_CR|  ACCOUNTED_CR|  LINE_DESCRIPTION|  CUSTOMER_NAME|  CUSTOMER_NUMBER|  SITE_NUMBER|  GBL_QUANTITY| DISCOUNT_TYPE| PRODUCT_CODE| DEAL_NUMBER| UNITS| COMMENTS');
    FOR rec_xla_je_period IN c_xla_je_period LOOP
      FOR rec_xla_je IN c_xla_je(rec_xla_je_period.ae_header_id) LOOP
        vl_quantity      := NULL;
        vl_discount_type := NULL;
        vl_prod_code     := NULL;
        vl_deal_num      := NULL;
        vl_units         := NULL;
        vl_comments      := NULL;
        FOR rec_xla_ac IN c_xla_ac(rec_xla_je.ae_header_id,
                                   rec_xla_je.ae_line_num) LOOP
          IF rec_xla_ac.analytical_criterion_code = 'C_GBL_QUANTITY' THEN
            vl_quantity := rec_xla_ac.ac1;
          ELSIF rec_xla_ac.analytical_criterion_code = 'C_OID' THEN
            vl_discount_type := rec_xla_ac.ac1;
            vl_prod_code     := rec_xla_ac.ac2;
            vl_deal_num      := rec_xla_ac.ac3;
            vl_units         := rec_xla_ac.ac4;
            vl_comments      := rec_xla_ac.ac5;
          END IF;
        END LOOP;
        fnd_file.put_line(fnd_file.output,
                          rec_xla_je.ae_header_id || '|' || rec_xla_je.name || '|' ||
                          rec_xla_je.je_category_name || '|' ||
                          rec_xla_je.accounting_date || '|' ||
                          rec_xla_je.reference_date || '|' ||
                          rec_xla_je.description || '|' ||
                          rec_xla_je.ae_line_num || '|' ||
                          rec_xla_je.code_combination || '|' ||
                          rec_xla_je.accounting_class_code || '|' ||
                          rec_xla_je.currency_code || '|' ||
                          rec_xla_je.entered_dr || '|' ||
                          rec_xla_je.accounted_dr || '|' ||
                          rec_xla_je.entered_cr || '|' ||
                          rec_xla_je.accounted_cr || '|' ||
                          rec_xla_je.line_description || '|' ||
                          rec_xla_je.customer_name || '|' ||
                          rec_xla_je.customer_number || '|' ||
                          rec_xla_je.site_number || '|' || vl_quantity || '|' ||
                          vl_discount_type || '|' || vl_prod_code || '|' ||
                          vl_deal_num || '|' || vl_units || '|' ||
                          vl_comments);
     
      END LOOP;
    END LOOP;
  END sla_report;
END c_xla_je_load_pkg;


---------------------------------以下是WEBADI 定义-----------------------------------
Navigater: Desktop Integration Manager/Manager Integrators

1. Integrator

   Name: CUST XLA Journal Entry Load

   Code: C_XLA_JE_LOAD

   Application:  Custom Application

   Enabled: Y

   Display in Create Document Page: Y

   Security ruls:  function: C_XLA_JE_LOAD

2. Interfaces

   Name: C_XLA_JE_LOAD_STAGING

   Type: Table

   Entity name: C_XLA_JE_LOAD_STAGING (实际表名,注意,需在数据库中注册此表,否则找不到)

   无任何default value and LOV validation

3. Contents

   此步根据需要设置,如果希望用户导出的是空的模板文件,则无需设置,如果希望用户在导出的同时把数据文件加进去,则需设置,我这里设了一下:

   Name: C_XLA_JE_LOAD_CONTENT

   Type: TEXT

   Reporting: N

   做了这个设置后,后面还要做Mapping的设置,设置数据文件列与Interface列的对应关系

4. Uploader

   Select " Copy from Template "

   Uplaoder name: do not change

   Title: do not change

   Header: do not change

   修改bne: import (用来启动后面的importer 的运行)的定义

   Description: Automatically Submit CUST XLA Journal Import

   Prompt left: Automatically Submit CUST XLA Journal Import

   Default value : Y

   Enabled: N (必选且用户无法修改,因为如用户不选的话,后面的Request无法调用,需要人为手工调用)

5. Importer

   Importer type: Asynchronous concurrent request (导步请求,WEBADI无需等待请求执行结果)

   Importer name: C_XLA_JE_LOAD_IMPORTER

   Concurrent Program Request Submission: Program: CUST XLA Journal Entry Load (注意: 使用此WEBADI的职责需要能够调用此请求)

   Success Message Definition: CUST XLA Journal Import Request ID $import$.requestid

保存生成Integrator

----------------以下是Layout (必须)  及 Mapping(不必须) 的定义-------------------

最后需要定义Layout 及 Mapping (如无Contents定义,无需定义Mapping)

1. Layout

   Layout name: C_XLA_JE_LOAD_LAYOUT

   Set all columns to LINES

   Protect sheet: Y (Excel模板是否保护)

   Style. sheet: Default

   Apply filter: Y

   Data entry rows: 2000(此项是很大的一个改进,可以定义模板的初始行数,不再受10行的困扰了)

   定义字段顺序(此项定义本人觉得设计的很垃圾,直接用sequence number就行了,非要搞两个按钮去点,麻烦死了)

   定义显示宽度(此项也是很好的一个改进,增加了用户体验)

Mapping 定义略。

FNDLOAD  DOWNLOAD:

FNDLOAD apps/apps 0 Y DOWNLOAD bneintegrator.lct C_XLA_JE_LOAD.ldt
BNE_INTEGRATORS INTEGRATOR_ASN=C_APPS
INTEGRATOR_CODE=C_XLA_JE_LOAD_XINTG

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10359218/viewspace-702819/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-04

  • 博文量
    140
  • 访问量
    478335