Вызов формы FNDRSRUN для запуска канкарента с определенными параметрами

10 Апрель 2012 Нет комментариев

Открытие формы 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), через двоеточие.

FNDDSRUN Запуск канкарента ОЕБС

Читать дальше про “Вызов формы FNDRSRUN для запуска канкарента с определенными параметрами” »

ОЕБС Расширенный скрипт инициализации сессиии

6 Апрель 2012 3 comments

ОЕБС Инициализация сессии
ОЕБС Расширенный скрипт для инициализации сессии:

  • Выводит в output короткую запись для инициализации.
  • Если не заданы полномочия или приложение, то выводит в output список назначенных пользователю полномочий.
declare
 l_lang           varchar2(2)   := 'RU';
 l_user_name      varchar2(100) := 'USER_NAME';
 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; 
begin
 
 -- 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 := 'Библиотека объектов приложений';
 
   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,
                        --
                        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_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)
 
                 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||''';');
     end loop;
 
   else
 
     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;
     
     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;
 
     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
                               );
     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;');     
   end if;
end;

Информация о расчетных счетах — поставщик или заказчик

oebs_supplier_customer

Запрос для определения типа расчетного счета:

(поставщик или заказчик)

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 («Вид -> Запросы») — отображение конкретного запроса

16 Март 2012 14 comments

Для отображении формы FNDRSRUN («Вид -> Запросы») с конкретным запросом, используем полезный параметр DODT_REQ_ID.

FNDDSRUN
      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) || '"'
      );

Просмотр всех процедур PL/SQL package

29 Февраль 2012 1 комментарий

Для поиска определенных функций в 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

Скрипт копирования меню

27 Февраль 2012 Нет комментариев

Скрипт для копирования меню. В случае, если меню уже существует, то добавляются отсутствуюшие пункты меню.

DECLARE
 l_original_menu_name VARCHAR2(150) := 'MENU_NAME!!!';
 l_new_menu_name      VARCHAR2(150) := 'NEW_MENU_NAME!!!';
 l_new_user_menu_name VARCHAR2(400) := 'Описание меню!!!';
 --
 l_flag_exist_menu NUMBER := 0;
 l_new_menu_id     NUMBER;
 l_last_menu_id    NUMBER;
 l_row_id          VARCHAR2(100);
 l_descr           VARCHAR2(400);
 --
 l_req_status      VARCHAR2(1);
BEGIN
  
  -- получаем ID оригинального меню
  SELECT max(menu_id) into l_last_menu_id 
  FROM fnd_menus_vl where menu_name = l_original_menu_name;
  
  if (l_last_menu_id is null) then
    dbms_output.put_line('Не найдено оригинальное меню');
    return;
  else
    dbms_output.put_line('Original menu_id='||l_last_menu_id);    
  end if;
  
  -- =====================================================
  -- проверка на существование нового меню
  -- =====================================================
  SELECT max(menu_id) into l_new_menu_id FROM fnd_menus_vl
  where menu_name = l_new_menu_name;
  
  if (l_new_menu_id is not null) then
    l_flag_exist_menu := 1; -- меню уже существует
    dbms_output.put_line('Меню уже существует');
  else
    -- создаем новое меню
    l_new_menu_id := fnd_menus_s.nextval;
    
    FND_MENUS_PKG.INSERT_ROW(
        X_ROWID             => l_row_id,
        X_MENU_ID           => l_new_menu_id,
        X_MENU_NAME         => l_new_menu_name,
        X_USER_MENU_NAME    => l_new_user_menu_name,
        X_MENU_TYPE         => null,
        X_DESCRIPTION       => null,
        X_CREATION_DATE     => sysdate,
        X_CREATED_BY        => fnd_global.user_id,
        X_LAST_UPDATE_DATE  => sysdate,
        X_LAST_UPDATED_BY   => fnd_global.user_id,
        X_LAST_UPDATE_LOGIN => fnd_global.login_id);    
    dbms_output.put_line('Создали меню: '||l_new_menu_name);
    dbms_output.put_line('  menu_id: '||l_new_menu_id);
  end if;
  
  -- =====================================================  
  -- Создание пунктов меню
  -- =====================================================  
  for i in (select t.* from fnd_menu_entries_vl t where t.menu_id = l_last_menu_id)
  loop
    
    -- проверка на существование пункта меню
    select nvl(max(1),0) into l_flag_exist_menu
    from fnd_menu_entries t
    where t.menu_id = l_new_menu_id 
          and ( (i.sub_menu_id is null and t.sub_menu_id is null) or t.sub_menu_id = i.sub_menu_id)
          and ( (i.function_id is null and t.function_id is null) or t.function_id = i.function_id)
          and 1=1;

    -- создаем только новые пункты меню
    if (l_flag_exist_menu = 0) then
      FND_MENU_ENTRIES_PKG.INSERT_ROW(
       X_ROWID             => l_row_id,
       X_MENU_ID           => l_new_menu_id,
       X_ENTRY_SEQUENCE    => FND_MENUS_PKG.NEXT_ENTRY_SEQUENCE(), --i.entry_sequence,
       X_SUB_MENU_ID       => i.sub_menu_id,
       X_FUNCTION_ID       => i.function_id,
       X_GRANT_FLAG        => i.grant_flag,
       X_PROMPT            => i.prompt,
       X_DESCRIPTION       => i.description,
       X_CREATION_DATE     => sysdate,
       X_CREATED_BY        => fnd_global.user_id,
       X_LAST_UPDATE_DATE  => sysdate,
       X_LAST_UPDATED_BY   => fnd_global.user_id,
       X_LAST_UPDATE_LOGIN => fnd_global.login_id);  
       
       l_descr := 'Создан пункт';
       if (i.prompt is not null) then
         l_descr := l_descr ||' : '|| i.prompt;
       end if;
       if (i.description is not null) then
         l_descr := l_descr ||' : '|| i.description;
       end if;  
       if (i.sub_menu_id is not null) then
         l_descr := l_descr ||' : sub_menu_id='|| i.sub_menu_id;
       end if; 
       if (i.function_id is not null) then
         l_descr := l_descr ||' : function_id='|| i.function_id;
       end if;              
       dbms_output.put_line(l_descr);
    end if;
    
  end loop;   
  
  l_req_status := FND_MENU_ENTRIES_PKG.SUBMIT_COMPILE();
  dbms_output.put_line('SUBMIT_COMPILE='||l_req_status);
  
END;