APPS-ORACLE.RU

ОЕБС Пример вызова параллельной программы для сторнирования журналов

-- -------------------------------------------------------------------------------
-- Сторнирование журнала
PROCEDURE reverse_journals(x_return_status OUT VARCHAR2
                          ,x_mess          OUT VARCHAR2) 
IS
  TYPE t_integers IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
  l_request_ids   t_integers;
  l_result        BOOLEAN := TRUE;
  l_access_set_id NUMBER;
BEGIN
  write_log('START REVERSE_JOURNALS');
  x_return_status := 'E';
  l_access_set_id := fnd_profile.value('GL_ACCESS_SET_ID');
  FOR c_je IN (SELECT jh.je_header_id
                     ,jh.rowid        AS row_id
                     ,jh.name         AS journal_name
                     ,b.name          AS batch_name
                 FROM gl.gl_je_headers jh, gl_je_batches b
                WHERE 1 = 1
                  AND jh.je_batch_id = b.je_batch_id
                     --
                  AND jh.ledger_id = G_LEDGER_ID
                  AND jh.je_source = G_JE_SOURCE_NAME
                  AND jh.je_category = G_JE_CATEGORY_NAME
                  AND jh.period_name = P_PERIOD_NAME
                  AND (b.name LIKE g_bath_name_in || '%' OR
                      b.name LIKE g_bath_name_out || '%')
                  AND (jh.name LIKE g_jh_name_in || '%' OR
                      jh.name LIKE g_jh_name_out || '%')
                  AND ( --Неотсторнированные журналы
                       (jh.accrual_rev_status IS NULL AND
                 -- сторно на сторно на сторно ...
                       jh.reversed_je_header_id IS NULL) OR 
                       (jh.accrual_rev_status IS NULL AND
                       jh.reversed_je_header_id IS NOT NULL AND
                       MOD((SELECT COUNT(*)
                              FROM gl_je_headers jh0
                             START WITH jh0.je_header_id = jh.je_header_id
                            CONNECT BY jh0.je_header_id = PRIOR
                                       jh0.reversed_je_header_id)
                           ,2) != 0))
                ORDER BY jh.je_header_id)
  LOOP
    write_log('  Сторнирование журнала "' || c_je.journal_name ||
           '" из пакета "' || c_je.batch_name || '"');
    UPDATE gl_je_headers
       SET accrual_rev_flag             = 'Y'
          ,accrual_rev_period_name      = P_PERIOD_NAME
          ,accrual_rev_change_sign_flag = 'Y'
          ,accrual_rev_effective_date   = SYSDATE
     WHERE ROWID = c_je.row_id;
    --
    l_request_ids(l_request_ids.COUNT + 1) :=
     fnd_request.submit_request(application => 'SQLGL'
                                ,program     => 'GLPREV'
                                ,description => NULL
                                ,start_time  => NULL
                                ,sub_request => FALSE
                                ,argument1   => l_access_set_id
                                ,argument2   => c_je.je_header_id);
    COMMIT;
    IF l_request_ids(l_request_ids.COUNT) = 0 THEN
      write_log('Ошибка при отправке параллельного запроса Сторнирование журналов.');
      l_request_ids.DELETE(l_request_ids.COUNT);
      l_result := FALSE;
      --exit;
    END IF;
  END LOOP;
  --
  WHILE l_request_ids.COUNT > 0
  LOOP
    -- описание функции http://apps-oracle.ru/fnd_request_exec/#WAIT_CONCURRENT
    if NOT (wait_concur(l_request_ids(l_request_ids.FIRST),x_mess)) then
      write_log(x_mess);
      l_result := FALSE;
      exit;
    end if;
    l_request_ids.DELETE(l_request_ids.FIRST);
  END LOOP;
  write_log('END REVERSE_JOURNALS' || chr(10));
  --
  IF l_result THEN
    x_return_status := 'S';
    x_mess          := 'Сторнирование журналов завершено успешно.';
    RETURN;
  ELSE
    x_return_status := 'E';
    x_mess          := 'Сторнирование журналов завершено с ошибками. '||x_mess;
    RETURN;
  END IF;
  --
END reverse_journals;

ОЕБС Пример вызова параллельной программы для проведения журналов

-- -----------------------------------------------------
-- Проведение журналов
-- -----------------------------------------------------
PROCEDURE post_journals(p_ledger_id NUMBER
                       ,p_group_id  NUMBER
                       ,p_period    VARCHAR2
                       ,p_rev       NUMBER
                       ,x_return_status OUT VARCHAR2
                       ,x_mess          OUT VARCHAR2)
IS
  l_request_id           INTEGER;
  l_posting_run_id       INTEGER;
  l_chart_of_accounts_id INTEGER;
  l_access_set_id        INTEGER;
BEGIN
  write_log('START POST_JOURNALS');
  x_return_status  := 'E';
  l_posting_run_id := gl_je_posting_s.nextval;
  IF (p_rev = 0) THEN
    UPDATE gl_je_batches b
       SET status               = 'S'
          ,approval_status_code = 'Z' -- пакет должен быть утвержден
          ,posting_run_id       = l_posting_run_id
     WHERE b.status = 'U'
       AND b.GROUP_ID = p_group_id;
  ELSIF (p_rev = 1) THEN
    UPDATE gl_je_batches b
       SET status               = 'S'
          ,approval_status_code = 'Z' -- пакет должен быть утвержден
          ,posting_run_id       = l_posting_run_id
     WHERE b.status = 'U'
       AND EXISTS
     (SELECT 1
              FROM gl_je_batches jb, gl_je_headers h, gl_je_headers hr
             WHERE jb.je_batch_id = h.je_batch_id
               AND h.je_header_id = hr.reversed_je_header_id
               AND (h.name LIKE G_JH_NAME_IN || '%' OR
                   h.name LIKE G_JH_NAME_OUT || '%')
               AND h.period_name = p_period
               AND h.ledger_id = p_ledger_id
               AND hr.je_batch_id = b.je_batch_id)
       AND 2 = 2;
  END IF;
  write_log('  UPDATE ' || SQL%ROWCOUNT || ' rows in GL_JE_BATCHES');
  IF (SQL%ROWCOUNT > 0) THEN
    --
    SELECT l.chart_of_accounts_id, a.access_set_id
      INTO l_chart_of_accounts_id, l_access_set_id
      FROM gl_ledgers l, gl_access_sets a
     WHERE 1 = 1
       AND l.ledger_id = p_ledger_id
       AND a.default_ledger_id = l.ledger_id
       AND a.chart_of_accounts_id = l.chart_of_accounts_id
       AND a.period_set_name = l.period_set_name
       AND a.accounted_period_type = l.accounted_period_type;
 
    l_request_id := fnd_request.submit_request(
                 application => 'SQLGL'
                ,program     => 'GLPPOSS'
                ,description => NULL
                ,start_time  => NULL
                ,sub_request => FALSE
                ,argument1   => p_ledger_id
                ,argument2   => TO_CHAR(fnd_profile.value('GL_ACCESS_SET_ID'))
                ,argument3   => l_chart_of_accounts_id
                ,argument4   => l_posting_run_id);
 
    COMMIT;
    
    IF l_request_id = 0 THEN
      x_mess := 'Ошибка при отправке запроса проводки журналов';
      RETURN;
    END IF;
    
    -- описание функции http://apps-oracle.ru/fnd_request_exec/#WAIT_CONCURRENT
    if NOT (wait_concur(l_request_id,x_mess)) then
      write_log(x_mess);
      RETURN;
    end if;

  END IF;
 
  x_return_status := 'S';
  write_log('END POST_JOURNALS' || chr(10));
  RETURN;
EXCEPTION
  WHEN OTHERS THEN
    x_mess := 'Ошибка при запуске запроса проводки журналов: ' || SQLERRM;
END;