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; /

Комментариев нет: