FND: Create and Validate ICX Session Ticket

Following plsql block will create icx session ticket based on logged in user and his responsibility. If there is an existing session ticket, we can validate using API used in the block.

DECLARE
  l_user_name   VARCHAR2(50) := 'PANKAJ_MANDALIYA';
  l_resp_name  VARCHAR2(100) := 'Oracle Configurator Developer';
  l_user_id    NUMBER;
  l_resp_id    NUMBER;
  l_appl_id    NUMBER;
  l_icx_ticket  VARCHAR2(50);
  l_session_id VARCHAR2(50);
  l_result     VARCHAR2(50);
BEGIN
  SELECT fnd.user_id
  INTO l_user_id
  FROM fnd_user fnd
  WHERE fnd.user_name = l_user_name;
  
  SELECT DISTINCT fresp.responsibility_id,
    fresp.application_id
  INTO l_resp_id,
    l_appl_id
  FROM fnd_responsibility_tl fresp
  WHERE fresp.responsibility_name = l_resp_name;
  
  dbms_output.put_line('user_id = ' || l_user_id || ', resp_id = ' || l_resp_id || ', appl_id = ' || l_appl_id);
  
  fnd_global.apps_initialize(l_user_id,l_resp_id,l_appl_id);
  
  l_icx_ticket:=cz_cf_api.icx_session_ticket;
  dbms_output.put_line('ICX Ticket = ' || l_icx_ticket);
  
  l_session_id := fnd_session_utilities.XSID_to_SessionID(l_icx_ticket);
  dbms_output.put_line('Session Id = ' || l_session_id);
  
  l_result := fnd_session_management.check_session (p_session_id => l_session_id, p_resp_id => l_resp_id, p_app_resp_id => l_appl_id, p_tickle => 'N');
  dbms_output.put_line('Session validation result = ' || l_result);
END;

UI Templates used by Configurator UI

Following query returns the list of templates used by given UI. Replace ui_def_id in following query.


SELECT DISTINCT templ_ui_def_id,
  templ_id
FROM
  (SELECT ctrl_template_ui_def_id AS templ_ui_def_id,
     ctrl_template_id AS templ_id
   FROM cz_ui_page_elements pe
   WHERE ui_def_id = 113607
   AND deleted_flag = 0
   AND ctrl_template_id IS NOT NULL
   AND ctrl_template_ui_def_id IS NOT NULL
   AND EXISTS
    (SELECT 1
     FROM cz_ui_templates
     WHERE seeded_flag = 0
     AND deleted_flag = 0
     AND template_id = pe.ctrl_template_id
     AND ui_def_id = pe.ctrl_template_ui_def_id)
  UNION ALL
   SELECT page_status_templ_uidef_id AS templ_ui_def_id,
     page_status_template_id AS templ_id
   FROM cz_ui_pages pg
   WHERE ui_def_id = 113607
   AND deleted_flag = 0
   AND page_status_templ_uidef_id IS NOT NULL
   AND page_status_template_id IS NOT NULL
   AND EXISTS
    (SELECT 1
     FROM cz_ui_templates
     WHERE seeded_flag = 0
     AND deleted_flag = 0
     AND template_id = pg.page_status_template_id
     AND ui_def_id = pg.page_status_templ_uidef_id)
  UNION ALL
   SELECT ref_template_ui_def_id AS templ_ui_def_id,
     ref_template_id AS templ_id
   FROM cz_ui_ref_templates r
   WHERE deleted_flag = 0 START WITH deleted_flag = 0
   AND ref_templ_seeded_flag = 0
   AND(EXISTS
    (SELECT 1
     FROM cz_ui_page_elements pe
     WHERE pe.ui_def_id = 113607
     AND pe.deleted_flag = 0
     AND pe.ctrl_template_ui_def_id = r.template_ui_def_id
     AND pe.ctrl_template_id = r.template_id) OR EXISTS
      (SELECT 1
       FROM cz_ui_pages pg
       WHERE pg.ui_def_id = 113607
       AND pg.deleted_flag = 0
       AND pg.page_status_templ_uidef_id = r.template_ui_def_id
       AND pg.page_status_template_id = r.template_id)
    )
  CONNECT BY deleted_flag = 0
   AND ref_templ_seeded_flag = 0
   AND template_ui_def_id = PRIOR ref_template_ui_def_id
   AND template_id = PRIOR ref_template_id) ;