OEBS Программа – сторнирование журналов SQLGL GLPREV
13 Ноябрь 2012
Нет комментариев
ОЕБС Пример вызова параллельной программы для сторнирования журналов
-- -------------------------------------------------------------------------------
-- Сторнирование журнала
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;
Categories: API, Oracle e-Business Suite GLPREV, SQLGL, submit_request, wait_concur, параллельная программа, сторно
Последние комментарии