JRAD doc queries

Very useful which can help you find lot of issues related to missing intl txt or publication issue or identify corrupted ui template

--Debug the missing intl_text_id, find it in attribute values
select * from JDR_ATTRIBUTES where att_value like '%8581175%';

--use above component id to get document name
select jdr_mds_internal.getdocumentname(95614) from dual;

--use this to find the template element causing issue by checking sequence id
select * from jdr_components where comp_docid in (95614);

--use above jdr document name to find the content template
select * from cz_ui_templates where jrad_doc='/oracle/apps/cz/runtime/oa/webui/templates/regions/6448_162';

--check which page is referring this template
select * from cz_ui_page_elements where ctrl_template_id=6448;


select * from cz_localized_texts where localized_str like '%';

select * from cz_ui_defs where ui_Def_id in (62791);

select * from cz_ui_templates where ui_Def_id in (62791);

select * from fnd_new_messages where message_text like '%Message Text Here%';

Using Custom styles in Configurator UI

You can define custom css classes in UI and use it anywhere in UI Element as style.

Add Raw text in the Page and add following in Text Expression. Notice the dot in beginning of class name. This is same as how you define style in standard HTML page using CSS.

<style>
.xxhighlight {
    font-weight: bold;
    background-color: yellow;
}
</style>

Now use above class in style class anywhere in UI element. You will only put xxhighlight in it.
Style = xxhighlight

You can learn more about adding custom style classes in google. You can add more attributes to classes as you want.

Now look and feel of customization and style will be limited to your imagination and not what Configurator UI is offering.

Configurator model logic, UI update and re publication block

DECLARE
  l_model_id NUMBER;
  l_user_id number;
  CURSOR ui_name_cur
  IS
    SELECT ui_def_id,
      name
    FROM cz_ui_Defs
    WHERE devl_project_id=l_model_id
    AND deleted_flag     =0
    AND name LIKE 'MODEL%SUBTAB_UI';
  CURSOR pub_data_cur
  IS
    SELECT publication_id,
      cs.instance_name
    FROM cz_model_publications mp,
      cz_servers cs
    WHERE object_id       =l_model_id
    AND source_target_flag='S'
    AND deleted_Flag      =0
    AND disabled_flag     =0
    AND mp.server_id      =cs.server_local_id
    AND cs.instance_name  ='MYINSTANCE' ;
 
    CURSOR fnd_user_data_cur
  IS
    SELECT user_id from fnd_user where user_name = 'PANKAJ_MANDALIYA';
 
  CURSOR model_data_cur
  IS
    SELECT pn.name model_name,
      dp.name Description,
      dp.checkout_time,
      dp.checkout_user,
      rp.enclosing_folder folder_id,
      pn.devl_project_id model_id
    FROM cz_ps_nodes pn,
      cz_rp_entries rp,
      cz_devl_projects dp
    WHERE pn.ps_node_id   = rp.object_id
    AND rp.deleted_flag   = 0
    AND rp.object_type    = 'PRJ'
    AND dp.devl_project_id=pn.devl_project_id
    AND dp.devl_project_id=rp.object_id
    AND dp.deleted_flag   =0
    AND dp.name NOT LIKE 'Copy%'
    AND rp.name NOT LIKE 'Copy%'
    AND (pn.name LIKE 'MODEL1%')
    AND pn.deleted_flag = 0
    ORDER BY pn.name;
  v_model_data model_data_cur%rowtype;
  v_ui_data ui_name_cur%rowtype;
  v_pub_data pub_data_cur%rowtype;

  l_request_id   NUMBER;
  v_return_status VARCHAR2(10);
  v_msg_count number;
  v_msg_data      VARCHAR2(255);
  v_output_line   VARCHAR2(512);
  v_ui_def_id     NUMBER;
  v_new_pub_id number;
BEGIN
  -- Initialize Apps
  OPEN fnd_user_data_cur;
  FETCH fnd_user_data_cur INTO L_USER_ID;
  fnd_global.apps_initialize (L_USER_ID ,22687 ,708 );
  OPEN model_data_cur;
  FETCH model_data_cur INTO v_model_data;
  WHILE model_data_cur%FOUND
  LOOP
    l_model_id    := v_model_data.model_id;
    v_output_line := v_model_data.Model_Name || ',' || REPLACE(v_model_data.Description,',',' ') ||',' || v_model_data.Folder_ID || ',' || v_model_data.Model_ID ||',' || NVL(v_model_data.checkout_user, 'Unlocked') || ',' || v_model_data.checkout_time;
    --DBMS_OUTPUT.PUT ('Model name '||v_model_data.Model_Name);
    --DBMS_OUTPUT.PUT (', Folder '||v_model_data.Folder_ID);
    --DBMS_OUTPUT.PUT_LINE (', Model ID is '||v_model_data.Model_ID);
    --    l_request_id := fnd_request.submit_request ('CZ' ,--IN VARCHAR2 DEFAULT NULL,                -- application
    --    'CZBOMSIM',                                        -- IN VARCHAR2 DEFAULT NULL,-- program short name
    --    '',                                                -- description
    --    '',                                                -- start time
    --    FALSE,                                             -- IN BOOLEAN DEFAULT FALSE,                -- sub request
    --    'MYINSTANCE',                                         -- IN VARCHAR2 DEFAULT CHR (0),          -- argument1
    --    v_model_data.Folder_ID,                                          -- IN VARCHAR2 DEFAULT CHR (0),          -- argument2
    --    v_model_data.Model_ID,                                -- IN VARCHAR2 DEFAULT CHR (0),                  -- argument3
    --    CHR (0)                                            -- represents end of arguments
    --    );
    --    COMMIT;
    CZ_PB_MGR.IS_MODEL_UPTO_DATE( P_MODEL_ID =>l_model_id, X_RETURN_STATUS =>v_return_status, X_MSG_DATA =>v_msg_data);
    IF v_return_status ='1' THEN
      v_output_line   := v_output_line || ',' || 'Logic is upto date';
    ELSE
      v_output_line := v_output_line || ',' || 'Generate Logic is Required';
    END IF;
    OPEN ui_name_cur;
    FETCH ui_name_cur INTO v_ui_data;
    IF ui_name_cur%NOTFOUND THEN
      v_output_line := v_output_line || ',' || 'UI Not Found';
    ELSE
      CZ_PB_MGR.is_ui_upto_date (p_ui_def_id => v_ui_data.ui_def_id, X_RETURN_STATUS =>v_return_status, X_MSG_DATA =>v_msg_data);
      IF v_return_status<>'0' THEN
        v_output_line   := v_output_line || ',' || v_ui_data.name || ' Requires UI Refresh';
      ELSE
        v_output_line := v_output_line || ',' || v_ui_data.name || ' is upto date';
      END IF;
    END IF;
    CLOSE ui_name_cur;
 
    OPEN pub_data_cur;
    FETCH pub_data_cur INTO v_pub_data;
    IF pub_data_cur%NOTFOUND THEN
      v_output_line := v_output_line || ',' || 'No Existing Publication Found';
    ELSE
      --cz_pb_mgr.create_republish_publication(p_publication_id=> v_pub_data.publication_id, x_new_publication_id=> v_new_pub_id, x_return_status=>v_return_status, x_msg_count=>v_msg_count, x_msg_data=>v_msg_data);
      IF v_return_status<>FND_API.G_RET_STS_SUCCESS THEN
        v_output_line   := v_output_line || ',' || 'Republish Failed:' || v_msg_data;
      ELSE
        v_output_line := v_output_line || ',' || v_new_pub_id;
        --run publication concurrent
     
      END IF;
    END IF;
    CLOSE pub_data_cur;
 
 
    dbms_output.put_line(v_output_line);
    FETCH model_data_cur INTO v_model_data;
  END LOOP;
  CLOSE model_data_cur;
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) ;
 

Fixing Dropdown Size in Configurator UI

In Configurator UI, dropdown UI element can be used to display 0/1 option feature's option in UI. In Configurator, we do not have control to specify width of the dropdown. It is automatically adjusted to accomodate largest string in its optoins. I have seen response from Bruce Bowers (of Sun microsystem, now Oracle) on this in one of the forum.

Here is a response from Bruce Bowers on this.
The following idea can be extended to tweak other styles in Configurator as well.

------------------

We are currently on 11.5.10.  I actually examined the HTML code generated for dropdowns by the OA Framework (see below).  The HTML select element (dropdown) is actually embedded in a HTML table for some reason (perhaps it's necessary for the Partial Page Refresh functionality.)  This embedding makes it harder to style the dropdown itself.  However, the select element does have a class attribute with the value "x4".  Therefore, you can give all of the dropdowns on a page the same width (or other style property).  To do this, add a Raw Text UI element with the following text:

<style>
select.x4 {width:200px;}
</style>

This is not strictly conformant to the HTML standard (because style elements are only supposed to appear in the head of the HTML page), and it is not guaranteed to work from Oracle release to Oracle release, but it is accepted by most browsers, and we have been using this general approach for several years without issues.

Bruce

-----

HTML code generated for a Configurator Dropdown List UI element:

<table id="czUiNode_1__xc_" border="0" cellspacing="0" cellpadding="0">
    <tr>
        <td align="right" nowrap></td>
        <td></td>
        <td valign="top" nowrap>
            <script>function _uixspu(f,v,e,s,pt,p,o){if (!o) o=new Object();o.event=(e)?e:'update';if (s) o.source=s;if (p) { o.partial='true';if (pt) o.partialTargets=pt;_submitPartialChange(f,v,o);} else submitForm(f,v,o); }</script>
            <select id="czUiNode_1" class="x4" onchange="_uixspu('DefaultFormName',1,'update','17840',0,'true',{'_FORM_SUBMIT_BUTTON':'_fwkActBtnName_17840_update','evtSrcRowIdx':'0','evtSrcRowId':'CZApplicationModule.OF_1_PG0row3'});return false;" name="czUiNode_1">
                <option></option>
                <option value="opt9432">[Text Source]</option>
                <option value="opt13683">[Text Source]</option>
                <option value="opt9433">[Excluded Items Prefix] [Text Source] [Excluded Items Suffix]</option>
            </select>
        </td>
    </tr>
</table>