批量打开库存会计期SQL

由于系统界面中只能一次性打开一个库存组织下的一个会计期，如果需要打开大量的库存会计期时费时又费力，

应一个业务顾问朋友的要求，写了一段SQL脚本来实现将账簿下所有OU下的所有库存组织的会计期都打开：

 

DECLARE
  l_last_scheduled_close_date DATE;
  l_le_sysdate                DATE;
  —
  l_prior_period_open     BOOLEAN;
  l_new_acct_period_id    NUMBER;
  l_duplicate_open_period BOOLEAN;
  l_commit_completed      BOOLEAN;
  l_return_status         VARCHAR2(1);
BEGIN
  — operation units
  FOR ou IN (SELECT organization_id org_id
               FROM hr_operating_units
              WHERE set_of_books_id = fnd_profile.VALUE(‘GL_SET_OF_BKS_ID’) — 62
              ) LOOP
    l_le_sysdate := inv_le_timezone_pub.get_le_sysdate_for_ou(ou.org_id);
    — inventory organization
    FOR org IN (SELECT organization_id
                  FROM org_organization_definitions
                 WHERE operating_unit = ou.org_id
                   –AND organization_id = 458
                 ORDER BY organization_id) LOOP
      SELECT nvl(MAX(schedule_close_date), l_le_sysdate)
        INTO l_last_scheduled_close_date
        FROM org_acct_periods
       WHERE organization_id = org.organization_id;
      FOR ps IN (SELECT sob.period_set_name,
                        sob.accounted_period_type,
                        ps.period_name,
                        ps.period_year,
                        ps.period_num period_number,
                        ps.end_date
                   FROM gl_sets_of_books sob, gl_period_statuses ps
                  WHERE sob.set_of_books_id = fnd_profile.VALUE(‘GL_SET_OF_BKS_ID’) — 62
                    AND sob.set_of_books_id = ps.set_of_books_id
                    AND ps.application_id = 401
                    AND ps.adjustment_period_flag = ‘N’
                    AND (ps.period_name, ps.period_year) NOT IN
                        (SELECT period_name, period_year
                           FROM org_acct_periods
                          WHERE organization_id = org.organization_id)
                    AND ps.created_by <> 0
                  ORDER BY ps.period_year, ps.effective_period_num) LOOP
        — open the period
        cst_accountingperiod_pub.open_period(p_api_version               => 1.0,
                                             p_org_id                    => org.organization_id,
                                             p_user_id                   => fnd_global.user_id,
                                             p_login_id                  => fnd_global.login_id,
                                             p_acct_period_type          => ps.accounted_period_type,
                                             p_org_period_set_name       => ps.period_set_name,
                                             p_open_period_name          => ps.period_name,
                                             p_open_period_year          => ps.period_year,
                                             p_open_period_num           => ps.period_number,
                                             x_last_scheduled_close_date => l_last_scheduled_close_date,
                                             p_period_end_date           => ps.end_date,
                                             x_prior_period_open         => l_prior_period_open,
                                             x_new_acct_period_id        => l_new_acct_period_id,
                                             x_duplicate_open_period     => l_duplicate_open_period,
                                             x_commit_complete           => l_commit_completed,
                                             x_return_status             => l_return_status);
        IF l_commit_completed THEN
          dbms_output.put_line(‘Period: (‘ || ps.period_name || ‘) is success’);
        ELSE
          dbms_output.put_line(‘Period: (‘ || ps.period_name || ‘) is failed’);
          GOTO stop_open;
        END IF;
      END LOOP;
    END LOOP;
  END LOOP;
  <<stop_open>> NULL;
END;

 

运行这段脚本，需要在同一个Session中进行Apps的初始化，否则无法取到环境信息