APPS-ORACLE.RU

Весьма полезная 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 
                       )

Пример использования 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;

Пример использования API для получения текущего или доступного для резервирования количества для позиции:

declare
  p_organization_id   NUMBER := 155;
  p_inventory_item_id NUMBER := 546;
  --
  p_revision          VARCHAR2(3);
  p_lot_number        VARCHAR2(80);
  p_subinventory_code VARCHAR2(10) := 'VAlVALVAL';
  p_locator_id        NUMBER;  
  --
  lx_qoh    NUMBER; -- Total Quantity (Onhand)
  lx_rqoh   NUMBER;
  lx_qr     NUMBER;
  lx_qs     NUMBER;
  lx_att    NUMBER; -- Available to Transact
  lx_atr    NUMBER; -- Available to Reserve
  --
  lx_return_status VARCHAR2(1);
  lx_msg_count     NUMBER;
  lx_msg_data      VARCHAR2(4000);
begin
  inv_quantity_tree_grp.clear_quantity_cache;
  inv_quantity_tree_pub.query_quantities(
        p_api_version_number         => 1.0,
        p_init_msg_lst               => fnd_api.g_false,
        x_return_status              => lx_return_status,
        x_msg_count                  => lx_msg_count,
        x_msg_data                   => lx_msg_data,
        --
        p_organization_id            => p_organization_id,
        p_inventory_item_id          => p_inventory_item_id,
        p_tree_mode                  => apps.inv_quantity_tree_pub.g_transaction_mode,
        p_is_revision_control        => case when p_revision is null   then FALSE else TRUE end,
        p_is_lot_control             => case when p_lot_number is null then FALSE else TRUE end,
        p_is_serial_control          => FALSE,
                                         
        p_revision                   => p_revision,
        p_lot_number                 => p_lot_number,
        p_subinventory_code          => p_subinventory_code,
        p_locator_id                 => p_locator_id,
                                         
        x_qoh                        => lx_qoh,  -- OnHand Quantity
        x_rqoh                       => lx_rqoh,
        x_qr                         => lx_qr,
        x_qs                         => lx_qs,
        x_att                        => lx_att, -- Available To Transact
        x_atr                        => lx_atr  -- Available To Reserve
                                        );
   if lx_return_status ='S' then
     DBMS_OUTPUT.put_line ('Общее количество: ' || lx_qoh);
     DBMS_OUTPUT.put_line ('Доступно для резервирования: ' || lx_atr);
     DBMS_OUTPUT.put_line ('Доступно для транзакции: ' || lx_att);
   else
    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 for p_inventory_item_id='||p_inventory_item_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;   
                                           
end;

Так же есть запрос для просмотра текущего количества Модуль INV – Текущее количество для позиции

Если при построении отчета XML Publisher Reports получили ошибку типа:
****Warning!!! Due to high volume of data, got out of memory exception…***
****Please retry with scalable option or modify the Data template to run in scalable mode…***

то могут помочь следующие параметры:

Заходим в параллельную программу и в поле «Параметры» ставим одно из значений

(a) -Xmx512m -Xss1024k
(b) -Xmx512m -Xss2048k
(c) -Xmx512m -Xss4096k
(d) -Xmx1024m -Xss1024k
(e) -Xmx1024m -Xss2048k
(f) -Xmx1024m -Xss4096k

-Xmx (-mx): maximum heap size
-Xss : Java stack size

Скрипт обновления параметров профиля. В данном примере изменяется поле SQL_VALIDATION.

declare
  l_sql VARCHAR2(4000);
begin
           
  l_sql := 'SQL="SELECT LOOKUP_CODE, MEANING \"Включить проверку\"
            INTO :PROFILE_OPTION_VALUE, :VISIBLE_OPTION_VALUE
            FROM FND_LOOKUP_VALUES_VL
            WHERE LOOKUP_TYPE = ''!!!LOOKUP_NAME''
                  AND ENABLED_FLAG = ''Y''
                  AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE)
                      AND NVL(END_DATE_ACTIVE,SYSDATE)
            ORDER BY MEANING"
            COLUMN="\"Включить проверку\"(20)"';

  for i in (select profile_option_name
                 , user_profile_option_name
                 , start_date_active
                 , end_date_active
                 , user_changeable_flag
                 , user_visible_flag
                 , read_allowed_flag
                 , write_allowed_flag
                 , site_enabled_flag
                 , site_update_allowed_flag
                 , app_enabled_flag
                 , app_update_allowed_flag
                 , resp_enabled_flag
                 , resp_update_allowed_flag
                 , user_enabled_flag
                 , user_update_allowed_flag
                 , (select a.application_short_name 
                    from fnd_application a 
                    where a.application_id = t.application_id
                    ) as appl_short_name
            from fnd_profile_options_vl t
            where profile_option_name like '!!!%'
          )
  loop
   dbms_output.put_line('update '||i.profile_option_name);
   fnd_profile_options_pkg.load_row (
                   x_profile_name             => i.profile_option_name,
                   x_owner                    => '!!!XX',
                   x_application_short_name   => i.appl_short_name,
                   x_user_profile_option_name => i.user_profile_option_name,
                   x_description              => i.user_profile_option_name,
                   x_user_changeable_flag     => i.user_changeable_flag,
                   x_user_visible_flag        => i.user_visible_flag,
                   x_read_allowed_flag        => i.read_allowed_flag,
                   x_write_allowed_flag       => i.write_allowed_flag,
                   x_site_enabled_flag        => i.site_update_allowed_flag,
                   x_site_update_allowed_flag => i.site_update_allowed_flag,
                   x_app_enabled_flag         => i.app_enabled_flag,
                   x_app_update_allowed_flag  => i.app_update_allowed_flag,
                   x_resp_enabled_flag        => i.resp_enabled_flag,
                   x_resp_update_allowed_flag => i.resp_update_allowed_flag,
                   x_user_enabled_flag        => i.user_enabled_flag,
                   x_user_update_allowed_flag => i.user_update_allowed_flag,
                   x_start_date_active        => to_char(i.start_date_active,'YYYY/MM/DD'),
                   x_end_date_active          => to_char(i.end_date_active,'YYYY/MM/DD'),
                   x_sql_validation           => l_sql
                                    );
    dbms_output.put_line('done');
  end loop;
  
end;

Расшифровка STATUS_CODE и PHASE_CODE из таблицы канкарентов FND_CONCURRENT_REQUESTS

PHASE_CODE:

select  lookup_code
       ,max(decode(LANGUAGE, 'RU',meaning,null )) as meaning_ru
       ,max(decode(LANGUAGE, 'US',meaning,null )) as meaning_us
from apps.FND_LOOKUP_VALUES
where lookup_type = 'CP_PHASE_CODE'
      and VIEW_APPLICATION_ID = 0
group by lookup_code;

STATUS_CODE:

select  lookup_code
       ,max(decode(LANGUAGE, 'RU',meaning,null )) as meaning_ru
       ,max(decode(LANGUAGE, 'US',meaning,null )) as meaning_us
from apps.FND_LOOKUP_VALUES
where lookup_type = 'CP_STATUS_CODE'
      and VIEW_APPLICATION_ID = 0
group by lookup_code;

Читать дальше про “Поля STATUS_CODE и PHASE_CODE из FND_CONCURRENT_REQUESTS” »

Вывести все View Objects (VO) и значения атрибутов для OAApplicationModule

    // Вывод всех значений VO
    public void printALLVO(OAApplicationModule am)
    {
      OADBTransaction tr = am.getOADBTransaction();
      if(tr.isLoggingEnabled(1)) 
      {
        tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", " ",1);
        tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", " AM "+am.getName(), 1);
      }
      String[] rootViewNames = am.getViewObjectNames();
      if(tr.isLoggingEnabled(1)) 
      {  
        tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", " count of VOs from AM = " +rootViewNames.length,1 );
      }
      for (int j =0 ;j < rootViewNames.length ;j++ )
      {
        if(tr.isLoggingEnabled(1)) 
        {  
          tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", " ",1);
          tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", "================",1);
          tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", " VO "+rootViewNames[j],1 );
        }
     
        oracle.jbo.ViewObject lVO = (oracle.jbo.ViewObject)am.findViewObject(rootViewNames[j]);
        if (lVO!=null && lVO.isExecuted()) {
          oracle.jbo.server.ViewRowImpl lVORow;
          int numRow = 1;
          for(lVORow = (oracle.jbo.server.ViewRowImpl)lVO.first(); lVORow != null;
              lVORow = (oracle.jbo.server.ViewRowImpl)lVO.next())
          {
              if (lVORow!=null) {
                  int attrCount = lVORow.getAttributeCount();
                  if(tr.isLoggingEnabled(1)) 
                  {              
                    tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", " ",1);
                    tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", " [row "+numRow+"]",1);
                    tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", " count of attributes = "+attrCount,1);
                  }
     
                  String[] attributeNames = lVORow.getAttributeNames();
                  for (int i = 0 ;i< attributeNames.length ;i++ )
                  {
                    if(tr.isLoggingEnabled(1)) 
                    {              
                      tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", attributeNames[i]
                      +" = "
                      +lVORow.getAttribute(i),1);
                    }
                  }
              }
              numRow ++;
          }
          if (numRow==1) {
            if(tr.isLoggingEnabled(1)) 
            {          
              tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", " not exists rows",1);
            }
          }
        }
        else {
          if(tr.isLoggingEnabled(1)) 
          {                
            tr.writeDiagnostics(this.getClass().getName() + ".printALLVO XX", " not executed",1);
          }
        }
      }              
     
    }

Цикл по вложенным Application Modules (текущий AM не входит во вложенные AM)

String nestedAMArray[]=oapagecontext.getRootApplicationModule().getApplicationModuleNames();
for(int i = 0; i < nestedAMArray.length; i++)
{
    String amName = nestedAMArray[i];
    OAApplicationModule amIter = (OAApplicationModule)am.findApplicationModule(amName);
    oapagecontext.writeDiagnostics(this,"Nested AM Name=>"+amName,1);
}    

Пример получения дочернего Application Module (AM) из контроллера

// Возвращает дочерний AM по его названию
public static OAApplicationModule getRequestedAM(OAPageContext oapagecontext,
                                                 String requestedAMName
                                                )  
{  
  OAApplicationModule rootAM    = oapagecontext.getRootApplicationModule();
  OAApplicationModule currentAM = null;  
  oapagecontext.writeDiagnostics(oapagecontext, "XX find applicationModule = "
                                                +requestedAMName,1);  
  String amName = "";  
  String nestedAMArray[] = rootAM.getApplicationModuleNames();  
  oapagecontext.writeDiagnostics(oapagecontext,"XX Root AM = "
                                 + rootAM.getName()
                                 + "; count child AMs = "
                                 + nestedAMArray.length,1);  
  for(int i = 0; i < nestedAMArray.length; i++)  
  {  
      amName = nestedAMArray[i];  
      currentAM = (OAApplicationModule)rootAM.findApplicationModule(amName); 
      String originalAmName = currentAM.getDefName();
      oapagecontext.writeDiagnostics(oapagecontext,"XX nested AM name = "
                                     +originalAmName,1);  
      if (originalAmName.equals(requestedAMName)) {
        oapagecontext.writeDiagnostics(oapagecontext,"XX found nested AM " 
                                       + originalAmName ,1);  
        break;  
      }  
  }  
  return currentAM;  
}