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;

Для выбора неотсторнированных журналов можно использовать следующий запрос:

SELECT  * FROM gl_je_batches        b,
               gl_je_headers        h,
               gl_je_lines          l,
               gl_code_combinations gcc
WHERE h.je_header_id = l.je_header_id
      AND b.je_batch_id  = h.je_batch_id
      AND gcc.code_combination_id = l.code_combination_id
      AND (  --Неотсторнированные журналы
            ( h.accrual_rev_status IS NULL AND
              h.reversed_je_header_id IS NULL
             )
             OR -- сторно на сторно на сторно ...
            ( h.accrual_rev_status IS NULL AND
              h.reversed_je_header_id IS NOT NULL
              AND MOD((SELECT COUNT(*)
                          FROM gl_je_headers jh
                         START WITH jh.je_header_id = h.je_header_id
                       CONNECT BY jh.je_header_id = PRIOR jh.reversed_je_header_id
                        ), 2 ) != 0
             )
          )