Для выбора неотсторнированных журналов можно использовать следующий запрос:
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
)
)
Скрипт для создания профиля в OEBS:
DECLARE
vr_Profile_name VARCHAR2(100) := 'PROFILE_CODE!!';
BEGIN
fnd_profile_options_pkg.load_row
(
x_profile_name => vr_Profile_name
, x_owner => 'INITIAL SETUP'
, x_application_short_name => 'APPL_CODE!!'
, x_user_profile_option_name => 'PROFILE_NAME!!'
, x_description => 'PROFILE_NAME!!'
, x_user_changeable_flag => 'Y'
, x_user_visible_flag => 'Y'
, x_read_allowed_flag => 'Y'
, x_write_allowed_flag => 'Y'
, x_site_enabled_flag => 'Y'
, x_site_update_allowed_flag => 'Y'
, x_app_enabled_flag => 'Y'
, x_app_update_allowed_flag => 'Y'
, x_resp_enabled_flag => 'Y'
, x_resp_update_allowed_flag => 'Y'
, x_user_enabled_flag => 'Y'
, x_user_update_allowed_flag => 'Y'
, x_start_date_active => to_char(sysdate,'yyyy/mm/dd')
, x_end_date_active => NULL
, x_sql_validation => 'SQL="SELECT LOOKUP_CODE, MEANING \"Включить проверку\"
INTO :PROFILE_OPTION_VALUE, :VISIBLE_OPTION_VALUE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = ''YES_NO''"
COLUMN="\"Включить проверку\"(20)"'
);
--
IF NOT
fnd_profile.save
(
x_name => vr_Profile_name
, x_value => 'Y'
, x_level_name => 'SITE'
)
THEN
DBMS_OUTPUT.Put_Line('Error in setting value');
END IF;
COMMIT;
END;
Для поиска определенных функций в plsql package можно использовать следующий запрос:
SQL> select object_name,
procedure_name
from ALL_PROCEDURES
where 1=1
and owner = 'APPS'
and object_name like 'FND%'
and object_type = 'PACKAGE'
and subprogram_id <> 0
--
and procedure_name like '%CANONICAL%'
/
OBJECT_NAME PROCEDURE_NAME
------------------------------ ------------------------------
FND_DATE CANONICAL_TO_DATE
FND_DATE DATE_TO_CANONICAL
FND_DATE STRING_TO_CANONICAL
FND_NUMBER CANONICAL_TO_NUMBER
FND_NUMBER NUMBER_TO_CANONICAL
FND_OAM_DSCFG_API_PKG GET_PROPERTY_CANONICAL_VALUE
FND_OAM_DSCFG_PROPERTIES_PKG GET_PROPERTY_CANONICAL_VALUE
FND_OAM_DSCFG_UTILS_PKG BOOLEAN_TO_CANONICAL
FND_OAM_DSCFG_UTILS_PKG CANONICAL_TO_BOOLEAN
FND_OAM_DSCFG_UTILS_PKG CANONICAL_TO_DATE
FND_OAM_DSCFG_UTILS_PKG CANONICAL_TO_NUMBER
FND_OAM_DSCFG_UTILS_PKG DATE_TO_CANONICAL
FND_OAM_DSCFG_UTILS_PKG NUMBER_TO_CANONICAL
13 rows selected
В процессе работы постоянно приходится дописывать в файлы пакетов команду «SHOW ERRORS».
Делать это постоянно крайне утомило, да и забывается иногда.
Настройками PL/SQL Developer это сделать не удалось, поэтому попробовал решить данный вопрос написав свой плагин.
Представляю вашему вниманию плагин MySave.
Описание: Сохраняет данные в файл, дописывает в конце «/» и «SHOW ERRORS».
Description: Use this plug-in to save file with ‘SHOW ERRORS’
Requirements: PL/SQL Developer 5.1.2 or newer
Пункт меню:
- File -> Save with show errors
- File -> Save with show errors as …
Install Plug-in
1) Download plug-in MySave
2) Run Setup.exe or Do copy file MySave.dll to folder PlugIns
allroundautomations.com
Запрос для просмотра настройки сегментов плана счетов
select sob.name Ledger_Name
,sob.ledger_id Ledger_Id
,sob.chart_of_accounts_id coa_id
,fifst.id_flex_structure_name struct_name
,ifs.segment_name
,ifs.application_column_name column_name
,sav1.attribute_value BALANCING
,sav2.attribute_value COST_CENTER
,sav3.attribute_value NATURAL_ACCOUNT
,sav4.attribute_value INTERCOMPANY
,sav5.attribute_value SECONDARY_TRACKING
,sav6.attribute_value GLOBAL
,ffvs.flex_value_set_name
,ffvs.flex_value_set_id
from fnd_id_flex_structures fifs
,fnd_id_flex_structures_tl fifst
,fnd_segment_attribute_values sav1
,fnd_segment_attribute_values sav2
,fnd_segment_attribute_values sav3
,fnd_segment_attribute_values sav4
,fnd_segment_attribute_values sav5
,fnd_segment_attribute_values sav6
,fnd_id_flex_segments ifs
,fnd_flex_value_sets ffvs
,gl_ledgers sob
where 1 = 1
and fifs.id_flex_code = 'GL#'
and fifs.application_id = fifst.application_id
and fifs.id_flex_code = fifst.id_flex_code
and fifs.id_flex_num = fifst.id_flex_num
and fifst.language = userenv('LANG')
and fifs.application_id = ifs.application_id
and fifs.id_flex_code = ifs.id_flex_code
and fifs.id_flex_num = ifs.id_flex_num
and sav1.application_id = ifs.application_id
and sav1.id_flex_code = ifs.id_flex_code
and sav1.id_flex_num = ifs.id_flex_num
and sav1.application_column_name = ifs.application_column_name
and sav2.application_id = ifs.application_id
and sav2.id_flex_code = ifs.id_flex_code
and sav2.id_flex_num = ifs.id_flex_num
and sav2.application_column_name = ifs.application_column_name
and sav3.application_id = ifs.application_id
and sav3.id_flex_code = ifs.id_flex_code
and sav3.id_flex_num = ifs.id_flex_num
and sav3.application_column_name = ifs.application_column_name
and sav4.application_id = ifs.application_id
and sav4.id_flex_code = ifs.id_flex_code
and sav4.id_flex_num = ifs.id_flex_num
and sav4.application_column_name = ifs.application_column_name
and sav5.application_id = ifs.application_id
and sav5.id_flex_code = ifs.id_flex_code
and sav5.id_flex_num = ifs.id_flex_num
and sav5.application_column_name = ifs.application_column_name
and sav6.application_id = ifs.application_id
and sav6.id_flex_code = ifs.id_flex_code
and sav6.id_flex_num = ifs.id_flex_num
and sav6.application_column_name = ifs.application_column_name
and sav1.segment_attribute_type = 'GL_BALANCING'
and sav2.segment_attribute_type = 'FA_COST_CTR'
and sav3.segment_attribute_type = 'GL_ACCOUNT'
and sav4.segment_attribute_type = 'GL_INTERCOMPANY'
and sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING'
and sav6.segment_attribute_type = 'GL_GLOBAL'
and ifs.id_flex_num = sob.chart_of_accounts_id
and ifs.flex_value_set_id = ffvs.flex_value_set_id
and sob.ledger_id = nvl(fnd_profile.value('GL_SET_OF_BKS_ID'), sob.ledger_id)
order by sob.name, sob.chart_of_accounts_id, ifs.application_column_name;
Простой, но довольно полезный запрос для определения группы запросов параллельной программы.
select rg.request_group_name
, rg.description
, cp.concurrent_program_name
, cp.user_concurrent_program_name
from fnd_request_group_units rgu
, fnd_concurrent_programs_vl cp
, fnd_request_groups rg
where 1=1
-- rg
and rg.request_group_id=rgu.request_group_id
-- cp
and cp.concurrent_program_id=rgu.request_unit_id
and cp.application_id=rgu.unit_application_id
and cp.concurrent_program_name like 'XX%'
order by rg.request_group_name, cp.concurrent_program_name
Объединенный скрипт создания пользователя включает в себя
- Создание пользователя
- Заполнение профилей
- Добавление полномочий
Читать дальше про “Объединенный скрипт создания пользователя” »
С помощью данного API можно добавить полномочие пользователю
begin
-- Call the procedure
fnd_user_pkg.addresp(username => :username,
resp_app => :resp_app,
resp_key => :resp_key,
security_group => :security_group, -- в основном 'STANDARD'
description => :description, -- любой коммент
start_date => :start_date,
end_date => :end_date);
commit;
dbms_output.put_line('Responsibility Added Successfully');
exception
when others then
dbms_output.put_line(' Responsibility is not added due to '
|| substr(SQLERRM, 1, 100));
rollback;
end;
И весьма полезный скрипт, c помощью которого можно быстро перетянуть полномочия с одного инстанса на другой.
Читать дальше про “API добавления полномочия для пользователя” »
Запрос для вывода иерархии организаций.
Список существующих иерархий можно увидеть в таблице hr.per_organization_structures.
SELECT LPAD(' ', 10 * (LEVEL - 1)) || org.name hierarchy,
org.organization_id
FROM hr_all_organization_units org, per_org_structure_elements pose
WHERE 1 = 1
AND org.organization_id = pose.organization_id_child
AND pose.org_structure_version_id = &p_structure_version_id
-- START WITH org.organization_id =
CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent
ORDER SIBLINGS BY org.location_id, pose.organization_id_child;
Запрос выводит информацию о сотруднике, основная таблица fnd_user.
SELECT u.user_id as user_id
, u.user_name as user_name
, pap.full_name as emp_name
, pap.employee_number as emp_number
, pj.name as job_name
--
FROM fnd_user u
, per_all_people_f pap
, per_assignments_f paf
, per_jobs pj
WHERE 1=1
--and u.user_id = fnd_global.user_id
and u.employee_id = pap.person_id(+)
and sysdate between pap.effective_start_date(+) and pap.effective_end_date(+)
-- per_assignments_f
and paf.person_id (+) = pap.person_id
and sysdate between paf.effective_start_date(+) and paf.effective_end_date(+)
and paf.primary_flag(+) = 'Y'
-- per_jobs
and pj.job_id(+) = paf.job_id;
Последние комментарии