oracle.notes: Open/close period script Oracle EBS

2013-11-27
Open/close period script Oracle EBS

Script uses concurrent GLOOAP
DECLARE
  -- какой год надо открыть
  l_year NUMBER := 2014;
  -- Сколько периодов в 1 году
  c_periods_in_year_cnt NUMBER := 5;
  l_iterations_cnt NUMBER;
  
  PROCEDURE open_period(p_sob_id gl_sets_of_books.set_of_books_id%TYPE) IS
    l_code gl_sets_of_books.short_name%TYPE;
    
    l_user_id fnd_user.user_id%TYPE;
    l_app_id fnd_responsibility_vl.APPLICATION_ID%TYPE;
    l_resp_id fnd_responsibility_vl.RESPONSIBILITY_ID%TYPE;
    
    l_req_num NUMBER;
  BEGIN
    SELECT b.short_name
    INTO l_code
    FROM gl_sets_of_books b
    WHERE b.set_of_books_id = p_sob_id;

    select user_id
    INTO l_user_id
    from fnd_user
    where user_name = 'SYSADMIN';

    select application_id,
      Responsibility_id
    INTO l_app_id, l_resp_id
    from fnd_responsibility_vl
    where responsibility_name like l_code || ' Суперпользователь ГК';

    fnd_global.APPS_INITIALIZE(user_id => l_user_id, resp_id => l_resp_id, resp_appl_id => l_app_id);

    l_req_num := fnd_request.submit_request('SQLGL', 'GLOOAP',
                                           '', '', FALSE,
                                           p_sob_id,
                                           '50268', 
                                           l_app_id, 
                                           'P', 'Y',chr(0));
  -- Параметры
  -- 50268 - chart_of_accounts_id
  -- определить идентификатор можно таким запросом 
  -- select * From FND_ID_FLEX_STRUCTURES_VL  where id_flex_code='GL#';
  -- 'P' -- execution_mode = P (хз, что такое)
  -- 'Y' -- хз, что такое
    dbms_output.put_line(l_req_num);                                           
    COMMIT;
  END;
BEGIN
  FOR rec IN (
      SELECT t.set_of_books_id, t.period_year, t.period_num
      FROM (
        select t.*, row_number() OVER (PARTITION BY t.set_of_books_id ORDER BY t.period_year DESC, t.period_num DESC) rn
        from GL.GL_PERIOD_STATUSES t
        WHERE t.closing_status = 'O') t
      WHERE rn = 1  
  ) LOOP
    l_iterations_cnt := GREATEST(l_year - rec.period_year, 0) * c_periods_in_year_cnt + (c_periods_in_year_cnt - rec.period_num);
    dbms_output.put_line('sob_id = ' || rec.set_of_books_id || '; Кол-во периодов ' || l_iterations_cnt);
    FOR i IN 1 .. l_iterations_cnt LOOP
      NULL;--open_period(rec.set_of_books_id);
    END LOOP;
  END LOOP;
END;
/
Комментариев нет:

Отправить комментарий