Иногда требуется отследить числа (визуально), с большим количеством знаков после точки.
Но столкнемся с тем, что PL/SQL Developer при отображении обрезает число.
select 1.000000000000001234567 v from dual
union all
select 1.000000100000001234567 from dual
union all
select 1.001000000000001234567 from dual
union all
select 1.123456789123456789123 from dual
В PL/SQL Developer есть полезная опция Number fields to_char
Читать дальше про “PL/SQL Developer — отображение чисел (number -> char)” »
Открытие формы FNDRSRUN с определенным канкарентом и выставленными параметрами:
1) Создаем свою форм функцию XX_FNDRSRUN для доступа к форме FNDRSRUN. (необязательный шаг)
2) Помещаем вызов нашего канкарента в меню Сервис на определенный SPECIAL.
3) Строка для вызова
CONCURRENT_PROGRAM_NAME ="<CODE>"
PROGRAM_APPL_SHORT_NAME="<APPL_CODE>"
CHAR1="<param1:param2:...>"
SUBMIT_ONCE="Y"
В параметр CHAR1 записываем параметры канкарента (value), через двоеточие.
Читать дальше про “Вызов формы FNDRSRUN для запуска канкарента с определенными параметрами” »
ОЕБС Расширенный скрипт для инициализации сессии:
- Выводит в output короткую запись для инициализации.
- Если не заданы полномочия или приложение, то выводит в output список назначенных пользователю полномочий.
declare
l_lang varchar2(2) := 'RU';
l_user_name varchar2(100) := 'AOSTAPISHEN';
l_resp_name varchar2(400);
l_resp_appl_name varchar2(400);
-- ------------------------------------------------------------------
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
l_appl_code VARCHAR2(10);
l_org_id NUMBER;
l_mo_sec_profile NUMBER;
l_mo_product number;
begin
-- l_resp_name := 'Управление активами предприятия';
-- l_resp_name := 'Диспетчер Дебиторов';
-- l_resp_name := 'Разработчик приложений';
-- l_resp_name := 'Движение денежных средств: суперпользователь';
-- l_resp_appl_name := 'Управление активами предприятия';
-- l_resp_appl_name := 'Главная книга';
-- l_resp_appl_name := 'Дебиторы'
-- l_resp_appl_name := 'Кредиторы';
-- l_resp_appl_name := 'Движение денежных средств';
-- l_resp_appl_name := 'Библиотека объектов приложений';
select user_id into l_user_id from fnd_user t where user_name like l_user_name;
if (l_resp_name is null or l_resp_appl_name is null) then
for i in (
SELECT ap.application_short_name as appl_code,
a.application_name as appl_name,
r.responsibility_name as resp_name,
r0.responsibility_key,
--
max(length(a.application_name )) over() as length_appl_name
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r,
fnd_responsibility r0,
fnd_application ap
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
and a.language = l_lang
and ap.application_id = a.application_id
and r.language = l_lang
and u.user_name = l_user_name
and r.responsibility_name <> 'Диагностика приложения'
and r.responsibility_name = nvl(l_resp_name,r.responsibility_name)
and a.application_name = nvl(l_resp_appl_name,a.application_name)
and r0.responsibility_id = r.responsibility_id
ORDER BY appl_name, resp_name
)
loop
dbms_output.put_line(rpad('l_resp_appl_name := '''||i.appl_name||'''',i.length_appl_name+22,' ') || ' ; l_resp_name := '''||i.resp_name||''';');
dbms_output.put_line(rpad('appl_code := '''||i.appl_code||'''',i.length_appl_name+22,' ') || ' ; responsibility_key := '''||i.responsibility_key||''';');
end loop;
else
select b.application_id, b.application_short_name into l_resp_appl_id, l_appl_code
from fnd_application_tl t
,fnd_application b
where 1=1
and t.application_name like l_resp_appl_name
and t.language = l_lang
and b.application_id = t.application_id;
select t.responsibility_id into l_resp_id from fnd_responsibility_tl t
where t.responsibility_name like l_resp_name and t.language = l_lang and t.application_id = l_resp_appl_id;
l_org_id := fnd_profile.VALUE_SPECIFIC(NAME => 'ORG_ID', RESPONSIBILITY_ID => l_resp_id, APPLICATION_ID=> l_resp_appl_id);
fnd_global.apps_initialize( user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_resp_appl_id
);
SELECT count(1) into l_mo_product FROM fnd_mo_product_init mpi where mpi.application_short_name = l_appl_code and nvl(mpi.status, 'N') = 'Y';
if (l_mo_product = 1) then
begin
MO_GLOBAL.init(l_appl_code);
if (l_org_id is not null) then
mo_global.set_policy_context('M',l_org_id);
end if;
l_mo_sec_profile := fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL');
mo_global.set_org_context(l_resp_id,l_mo_sec_profile,l_appl_code);
exception
when others then
dbms_output.put_line('SQLERRM:'||SQLERRM||chr(10));
dbms_output.put_line('FORMAT_ERROR_BACKTRACE:'||chr(10)||dbms_utility.format_error_backtrace);
end;
dbms_output.put_line('begin'||chr(10)||' fnd_global.apps_initialize('||l_user_id||', '||l_resp_id||', '||l_resp_appl_id||'); --'||l_resp_name||' - '||l_resp_appl_name
||chr(10)||' mo_global.init('''|| l_appl_code||''');'
||case when l_org_id is not null then chr(10)||' mo_global.set_policy_context(''M'','||l_org_id||');' end
||chr(10)||' mo_global.set_org_context('||l_resp_id||', '||l_mo_sec_profile||', '''||l_appl_code||''');'
||chr(10)||'end;');
else
dbms_output.put_line('begin'||chr(10)||' fnd_global.apps_initialize('||l_user_id||', '||l_resp_id||', '||l_resp_appl_id||'); --'||l_resp_name||' - '||l_resp_appl_name
||chr(10)||'end;');
end if;
end if;
end;
Запрос для определения типа расчетного счета:
(поставщик или заказчик)
SELECT account.bank_account_num
,account.ext_bank_account_id -- Link to Bank and Branch Information
,acc_instr.instrument_id
,case when ext_payer.ext_payer_id is not null then 'CUSTOMER'
when ext_iep.ext_payee_id is not null then 'SUPPLIER'
end type_account
,case when ext_payer.ext_payer_id is not null then
(select ac.CUSTOMER_ID || ' - ' || ac.CUSTOMER_NAME
from ar_customers ac where ac.CUSTOMER_ID = ext_payer.cust_account_id)
when ext_iep.ext_payee_id is not null then
(select ap.vendor_id || ' - ' || ap.vendor_name
from ap_suppliers ap where ap.party_id = ext_iep.payee_party_id)
end owner_account_name
FROM iby_ext_bank_accounts account
,iby_pmt_instr_uses_all acc_instr
,iby_external_payers_all ext_payer
,iby_external_payees_all ext_iep
WHERE 1 = 1
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id(+)
AND acc_instr.ext_pmt_party_id = ext_iep.ext_payee_id(+)
-- AND account.bank_account_num in ('','')
Для просмотра списка расчетных счетов заказчиков (покупателей, контрагентов) используем следующий запрос:
SELECT ac.CUSTOMER_id
, cust.party_name customer_name
, cust_acct.account_number
, cust_acct.cust_account_id
, cust_uses.site_use_code
, cust_loc.address1
, cust_loc.address2
, cust_loc.address3
, cust_loc.address4
, cust_loc.city
, cust_loc.postal_code
, bank.party_name bank_name
, bank_prof.home_country
, branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
, account.bank_account_num
, account.bank_account_name
, acc_instr.order_of_preference
, cust_uses.primary_flag
FROM hz_parties bank
, hz_relationships rel
, hz_parties branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
, iby_ext_bank_accounts account
, iby_external_payers_all ext_payer
, iby_pmt_instr_uses_all acc_instr
, hz_parties cust
, hz_cust_accounts cust_acct
, hz_cust_acct_sites_all cust_site
, hz_cust_site_uses_all cust_uses
, hz_party_sites party_site
, hz_locations cust_loc
, ar_customers ac
WHERE 1=1
AND bank.party_id = rel.object_id
and bank.party_type = rel.object_type
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.relationship_code = 'BRANCH_OF'
AND rel.subject_id = branch.party_id
AND rel.subject_type = branch.party_type
AND rel.subject_table_name = 'HZ_PARTIES'
AND bank.party_id = bank_prof.party_id
AND branch.party_id = branch_prof.party_id
AND cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
and cust_uses.status = 'A'
AND party_site.party_id = cust.party_id
AND party_site.party_site_id = cust_site.party_site_id
AND party_site.location_id = cust_loc.location_id
AND cust.party_id = cust_acct.party_id
AND bank.party_id = account.bank_id
AND branch.party_id = account.branch_id
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND ext_payer.cust_account_id = cust_acct.cust_account_id
AND cust_uses.site_use_id = ext_payer.acct_site_use_id
and ext_payer.cust_account_id = ac.CUSTOMER_id
Для получения списка расчетных счетов поставщиков используем следующий запрос
SELECT party_supp.party_name supplier_name
,aps.segment1 supplier_number
,ass.vendor_site_code supplier_site
,ieb.bank_account_num bank_account_num
,ieb.bank_account_name bank_account_name
,party_bank.party_name bank_name
,branch_prof.bank_or_branch_number bank_number
,party_branch.party_name branch_name
,branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
,ap_suppliers aps
,hz_party_sites site_supp
,ap_supplier_sites_all ass
,iby_external_payees_all iep
,iby_pmt_instr_uses_all ipi
,iby_ext_bank_accounts ieb
,hz_parties party_bank
,hz_parties party_branch
,hz_organization_profiles bank_prof
,hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.branch_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name, ass.vendor_site_code;
Для выбора неотсторнированных журналов можно использовать следующий запрос:
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;
Для отображении формы FNDRSRUN («Вид -> Запросы») с конкретным запросом, используем полезный параметр DODT_REQ_ID.
fnd_function.execute
(
function_name => 'FND_FNDRSRUN'
, open_flag => 'Y'
, session_flag => 'Y'
, other_params => 'MODE="VIEW" DODT_REQ_ID="' || to_char(l_request_id) || '"'
);
Для поиска определенных функций в 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
Последние комментарии