Весьма полезная JSP страница для авто входа в OEBS с логином и паролем:
http://host:port/OA_HTML/fndvald.jsp?username=!login&password=!pwd
Пример ссылки:
Читать дальше про “JSP страница для входа в OEBS с логином и паролем” »
Запрос для вывода списка полномочий и групп запросов по коду или имени отчета/параллельной программы:
with params as (
select 'FNDATREP' as short_name
,'' as long_name
,'RU' as lang /* RU;US */
from dual
)
select p1.concurrent_program_name
,pt.user_concurrent_program_name
,fa.application_short_name as appl_resp
,r.responsibility_key
,rt.responsibility_name
,fa1.application_short_name as appl_request_group
,g.request_group_name
from fnd_request_groups g,
fnd_request_group_units u,
fnd_application fa,
fnd_application fa1,
fnd_concurrent_programs_tl pt,
fnd_concurrent_programs p1,
fnd_responsibility r,
fnd_responsibility_tl rt,
params
where 1=1
and u.application_id = g.application_id
and u.request_group_id = g.request_group_id
and (u.request_unit_id = pt.concurrent_program_id
or u.request_unit_type = 'A')
and u.unit_application_id = pt.application_id
and pt.application_id = fa.application_id
and pt.concurrent_program_id = p1.concurrent_program_id
and g.application_id = fa1.application_id
and r.request_group_id = g.request_group_id
and r.responsibility_id = rt.responsibility_id
and rt.language = params.lang
and pt.language = params.lang
and (
(p1.concurrent_program_name like '%'||params.short_name||'%'
and params.short_name is not null
)
or
(pt.user_concurrent_program_name like '%'||params.long_name||'%'
and params.long_name is not null and params.short_name is null
)
)
order by 1,2;
Скрипт для вывода списка полномочий, в которых используется форма.
Так же добавлен путь внутри полномочий для вызова формы.
Желаемый язык для вывода данных вынесен в параметры.
with params as (
select 'FNDSCRSP' as form_name
,'' as user_form_name
,'' as description_form
,'RU' as lang /* RU;US */
from dual
)
select (select rt.responsibility_name from fnd_responsibility_tl rt , params
where rt.responsibility_id = r.responsibility_id
and rt.application_id = r.application_id
and rt.language = params.lang
) as responsibility_name
,(select ltrim(sys_connect_by_path(me.prompt, ' --> '),' --> ')
from (select b.menu_id,b.entry_sequence,b.sub_menu_id,b.function_id, t.prompt
from fnd_menu_entries_tl t, fnd_menu_entries b, params
where b.menu_id = t.menu_id
and b.entry_sequence = t.entry_sequence
and t.language = params.lang) me
where me.prompt is not null
and me.function_id = ff1.function_id
and rownum=1
start with me.menu_id = r.menu_id
connect by prior me.sub_menu_id = me.menu_id and prior me.prompt is not null
) path_to_form
,r.application_id
,r.responsibility_id
,r.responsibility_key
,r.menu_id
,(select mt.user_menu_name from fnd_menus_tl mt, params where mt.menu_id = r.menu_id and mt.language = params.lang) as user_menu_name
,ff1.function_id
,(select fft.user_function_name from fnd_form_functions_tl fft, params where fft.function_id = ff1.function_id and fft.language = params.lang) as user_function_name
,ff1.form_id
,(select ff.form_name from fnd_form ff where ff.form_id = ff1.form_id ) as form_name
,(select ftl.user_form_name from fnd_form_tl ftl, params where ftl.form_id = ff1.form_id and ftl.language = params.lang) as user_form_name
from fnd_responsibility r
,(select ff.function_id, ff.form_id from fnd_form_functions ff
where ff.form_id in (select f.form_id
from fnd_form f, fnd_form_tl ft, params
where f.form_id = ft.form_id
and (
f.form_name = params.form_name
or (ft.user_form_name like '%'||params.user_form_name||'%' and params.user_form_name is not null)
or (ft.description like '%'||params.description_form||'%' and params.description_form is not null)
)
)
) ff1
where 1=1
and sysdate between r.start_date and nvl(r.end_date, sysdate+1)
and r.menu_id in (select me.menu_id from fnd_menu_entries me
start with me.function_id = ff1.function_id
connect by prior me.menu_id = me.sub_menu_id
)
Пример SQL запроса для склонения слова «ошибка» для вывода количества ошибок: «Найдена 1 ошибка»; «Найдено 3 ошибки»; «Найдено 5 ошибок» :
select
val
,case when mod(val,10) between 2 and 4 then 'Найдено '||val||' ошибки'
when val between 11 and 14 or mod(val,10) between 5 and 9
or mod(val,10) = 0 then 'Найдено '||val||' ошибок'
when mod(val,10) = 1 then 'Найдена '||val||' ошибка'
end str
from ( select level val from dual connect by level<=25 )
VAL STR
---------- -------------------------------------------------------
1 Найдена 1 ошибка
2 Найдено 2 ошибки
3 Найдено 3 ошибки
4 Найдено 4 ошибки
5 Найдено 5 ошибок
6 Найдено 6 ошибок
7 Найдено 7 ошибок
8 Найдено 8 ошибок
9 Найдено 9 ошибок
10 Найдено 10 ошибок
11 Найдено 11 ошибок
12 Найдено 12 ошибки
13 Найдено 13 ошибки
14 Найдено 14 ошибки
15 Найдено 15 ошибок
16 Найдено 16 ошибок
17 Найдено 17 ошибок
18 Найдено 18 ошибок
19 Найдено 19 ошибок
20 Найдено 20 ошибок
21 Найдена 21 ошибка
22 Найдено 22 ошибки
23 Найдено 23 ошибки
24 Найдено 24 ошибки
25 Найдено 25 ошибок
25 rows selected
Пример использования API для создания заказа на перемещение
declare
lx_doc_hdr inv_move_order_pub.Trohdr_Rec_Type;
lx_doc_lines inv_move_order_pub.Trolin_Tbl_Type;
--
lx_doc_hdr_vals inv_move_order_pub.Trohdr_Val_Rec_Type;
lx_doc_lines_vals inv_move_order_pub.Trolin_Val_Tbl_Type;
lx_return_status varchar2(1);
lx_msg_data varchar2(4000);
lx_msg_count number;
begin
-- тип заказа на перемещение = заявка
lx_doc_hdr.move_order_type := inv_globals.G_MOVE_ORDER_REQUISITION;
lx_doc_hdr.organization_id := 1775;
lx_doc_hdr.date_required := sysdate;
lx_doc_hdr.operation := inv_globals.G_OPR_CREATE;
lx_doc_hdr.header_status := inv_globals.G_TO_STATUS_PREAPPROVED;
-- создание заголовка заказа на перемещение
inv_move_order_pub.create_move_order_header(
p_api_version_number => 1,
p_init_msg_list => fnd_api.G_TRUE,
p_return_values => fnd_api.G_TRUE,
p_commit => fnd_api.G_FALSE,
x_return_status => lx_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_trohdr_rec => lx_doc_hdr,
p_trohdr_val_rec => lx_doc_hdr_vals,
x_trohdr_rec => lx_doc_hdr,
x_trohdr_val_rec => lx_doc_hdr_vals
);
dbms_output.put_line('create_move_order_header='||lx_return_status);
dbms_output.put_line('header_id='||lx_doc_hdr.header_id);
if (lx_return_status!='S') then
FOR j IN 1 .. lx_msg_count LOOP
dbms_output.put_line('error('||j||')= ' ||fnd_msg_pub.get(j,fnd_api.g_false));
END LOOP;
else
lx_doc_lines(1).header_id := lx_doc_hdr.header_id;
lx_doc_lines(1).operation := inv_globals.G_OPR_CREATE;
lx_doc_lines(1).line_status := inv_globals.G_TO_STATUS_PREAPPROVED;
lx_doc_lines(1).date_required := sysdate;
lx_doc_lines(1).status_date := sysdate;
-- 64 Перенос заказа на перемещение
lx_doc_lines(1).transaction_type_id := inv_globals.g_type_transfer_order_subxfr;
--
lx_doc_lines(1).from_subinventory_code := 'ttttttt';
lx_doc_lines(1).organization_id := 1775;
lx_doc_lines(1).to_subinventory_code := 'fffffff';
lx_doc_lines(1).to_organization_id := 1775;
lx_doc_lines(1).inventory_item_id := 57878;
lx_doc_lines(1).uom_code := 'шт';
lx_doc_lines(1).quantity := 5;
-- создание строки заказа на перемещение
inv_move_order_pub.create_move_order_lines(
p_api_version_number => 1,
p_init_msg_list => fnd_api.G_TRUE,
p_return_values => fnd_api.G_TRUE,
p_commit => fnd_api.G_FALSE,
x_return_status => lx_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_trolin_tbl => lx_doc_lines,
p_trolin_val_tbl => lx_doc_lines_vals,
x_trolin_tbl => lx_doc_lines,
x_trolin_val_tbl => lx_doc_lines_vals
);
dbms_output.put_line('create_move_order_lines='||lx_return_status);
if (lx_return_status!='S') then
FOR j IN 1 .. lx_msg_count LOOP
dbms_output.put_line('error('||j||')='||fnd_msg_pub.get(j,fnd_api.g_false));
END LOOP;
else
dbms_output.put_line('line_id='||lx_doc_lines(1).line_id);
end if;
end if;
end;
Описание шагов по заполнение интерфейсных таблиц для создания спецификаций (комплектов позиций).
1) Таблица BOM_BILL_OF_MTLS_INTERFACE — заголовок
список обязательных полей:
2) Таблица BOM_INVENTORY_COMPS_INTERFACE — строки
список обязательных полей:
Читать дальше про “OEBS API BOM – Создание спецификаций через интерфейсные таблицы” »
Пример использования API для удаления резервирований позиции:
declare
l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
l_serial_number_tbl_type inv_reservation_global.serial_number_tbl_type;
--
lx_return_status varchar2(1);
lx_msg_data varchar2(4000);
lx_msg_count number;
begin
fnd_global.apps_initialize(-1, 20634, 401); --Запасы - Запасы
for i_cur in (select * from mtl_reservations t
where 1=1
and rownum=1
-- and ...
)
loop
l_rsv_rec.reservation_id := i_cur.reservation_id;
l_rsv_rec.organization_id := i_cur.organization_id;
l_rsv_rec.inventory_item_id := i_cur.inventory_item_id;
l_rsv_rec.demand_source_type_id := i_cur.demand_source_type_id;
l_rsv_rec.demand_source_header_id := i_cur.demand_source_header_id;
l_rsv_rec.demand_source_line_id := i_cur.demand_source_line_id;
l_rsv_rec.primary_uom_code := i_cur.primary_uom_code;
l_rsv_rec.reservation_uom_code := i_cur.reservation_uom_code;
-- l_rsv_rec.reservation_quantity := 1;--i_cur.reservation_quantity;
-- l_rsv_rec.primary_reservation_quantity := 1;--i_cur.primary_reservation_quantity;
l_rsv_rec.supply_source_type_id := i_cur.supply_source_type_id;
inv_reservation_pub.delete_reservation(p_api_version_number => 1.0
,p_rsv_rec => l_rsv_rec
,p_serial_number => l_serial_number_tbl_type
,x_return_status => lx_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
);
dbms_output.put_line(lx_return_status);
if (lx_return_status <> 'S') then
fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
p_count => lx_msg_count,
p_data => lx_msg_data);
dbms_output.put_line('ERROR delete_reservation for reservation_id ='
||l_rsv_rec.reservation_id ||':');
if lx_msg_count = 1 then
dbms_output.put_line(lx_msg_data);
else
FOR i IN 1 .. lx_msg_count
LOOP
dbms_output.put_line(fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false));
END LOOP;
end if;
end if;
commit;
end loop;
end;
Скрипт вывода столбцов таблицы в XML шаблон данных( BI Publisher Data Template )
declare
p_table_owner varchar2(30) := 'APPLSYS';
p_table_name varchar2(30) := 'FND_USER';
begin
for i_cur in (
select t.column_name
,t.data_type
,max(length(t.column_name)) over()
- length(t.column_name) as cur_gap_length
from all_tab_columns t
where 1=1
and t.owner = p_table_owner
and t.table_name = p_table_name
order by t.column_id
)
loop
dbms_output.put_line('<element name="' ||i_cur.column_name
||'"'||rpad(' ',i_cur.cur_gap_length)||' value="'||i_cur.column_name
||'" />');
end loop;
end;
-- Пример вывода
<element name="USER_ID" value="USER_ID" />
<element name="USER_NAME" value="USER_NAME" />
<element name="LAST_UPDATE_DATE" value="LAST_UPDATE_DATE" />
<element name="LAST_UPDATED_BY" value="LAST_UPDATED_BY" />
<element name="CREATION_DATE" value="CREATION_DATE" />
<element name="CREATED_BY" value="CREATED_BY" />
<element name="LAST_UPDATE_LOGIN" value="LAST_UPDATE_LOGIN" />
<element name="ENCRYPTED_FOUNDATION_PASSWORD" value="ENCRYPTED_FOUNDATION_PASSWORD" />
<element name="ENCRYPTED_USER_PASSWORD" value="ENCRYPTED_USER_PASSWORD" />
<element name="SESSION_NUMBER" value="SESSION_NUMBER" />
<element name="START_DATE" value="START_DATE" />
<element name="END_DATE" value="END_DATE" />
<element name="DESCRIPTION" value="DESCRIPTION" />
<element name="LAST_LOGON_DATE" value="LAST_LOGON_DATE" />
<element name="PASSWORD_DATE" value="PASSWORD_DATE" />
<element name="PASSWORD_ACCESSES_LEFT" value="PASSWORD_ACCESSES_LEFT" />
<element name="PASSWORD_LIFESPAN_ACCESSES" value="PASSWORD_LIFESPAN_ACCESSES" />
<element name="PASSWORD_LIFESPAN_DAYS" value="PASSWORD_LIFESPAN_DAYS" />
<element name="EMPLOYEE_ID" value="EMPLOYEE_ID" />
<element name="EMAIL_ADDRESS" value="EMAIL_ADDRESS" />
<element name="FAX" value="FAX" />
<element name="CUSTOMER_ID" value="CUSTOMER_ID" />
<element name="SUPPLIER_ID" value="SUPPLIER_ID" />
<element name="WEB_PASSWORD" value="WEB_PASSWORD" />
<element name="USER_GUID" value="USER_GUID" />
<element name="GCN_CODE_COMBINATION_ID" value="GCN_CODE_COMBINATION_ID" />
<element name="PERSON_PARTY_ID" value="PERSON_PARTY_ID" />
Oracle Application Framework: Пример кода для передачи параметра с кириллицей в URL.
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import oracle.apps.fnd.framework.OANLSServices;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
...
OAApplicationModuleImpl am = (OAApplicationModuleImpl)pageContext.getApplicationModule(webBean);
OANLSServices nls = am.getOADBTransaction().getOANLSServices();
try {
pAssignmentSet = URLEncoder.encode(pAssignmentSet, nls.getJAVAClientEncoding());
} catch (UnsupportedEncodingException e) {
throw new OAException(e.getMessage());
}
...
Пример использования API для перевода контрагента (таблица HZ_PAPTIES) с типом “PERSON” в статус “Неактивно”:
declare
p_party_id number := 17323325;
--
l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
l_object_version_number number;
--
x_profile_id NUMBER;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(4000);
begin
fnd_global.apps_initialize(-1, 20639, 200); --Диспетчер кредиторов - Кредиторы
l_party_rec.party_id := p_party_id;
l_party_rec.status := 'I';
l_person_rec.party_rec := l_party_rec;
select hp.object_version_number into l_object_version_number
from hz_parties hp where hp.party_id = l_party_rec.party_id;
hz_party_v2pub.update_person (
p_init_msg_list => fnd_api.g_false,
p_person_rec => l_person_rec,
p_party_object_version_number => l_object_version_number,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
dbms_output.put_line(x_return_status);
if (x_return_status <> 'S') then
fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
p_count => x_msg_count,
p_data => x_msg_data);
dbms_output.put_line('ERROR update for party_id='||l_party_rec.party_id||':');
if x_msg_count = 1 then
dbms_output.put_line(x_msg_data);
else
FOR i IN 1 .. x_msg_count LOOP
dbms_output.put_line(fnd_msg_pub.get(fnd_msg_pub.g_next,
fnd_api.g_false));
END LOOP;
end if;
end if;
commit;
end;
Последние комментарии