CZ: Republish the latest publication for given Model

This will republish the latest publication for given model. You can have various criteria to decide last publication.    

DECLARE

  P_API_VERSION NUMBER;

  P_PUBLICATION_ID NUMBER;

  P_USER_ID NUMBER;

  P_RESP_ID NUMBER;

  P_APPL_ID NUMBER;

  P_START_DATE DATE;

  P_END_DATE DATE;

  X_RUN_ID NUMBER;

  X_STATUS NUMBER;

BEGIN

  P_API_VERSION := 1.0;

  --P_PUBLICATION_ID := 123456;

  P_USER_ID := 1895; --PANKAJ_MANDALIYA 

  P_RESP_ID := 22687;

  P_APPL_ID := 708;

  P_START_DATE := SYSDATE;

  P_END_DATE := SYSDATE + 100;

 

  SELECT publication_id

  into p_publication_id

  FROM

    (SELECT publication_id

    FROM cz_model_publications

    WHERE object_id  =1070280 -- MODEL A

    AND disabled_flag=0

    AND deleted_Flag =0

    ORDER BY last_update_date DESC

    )

  WHERE rownum=1;

  CZ_MODELOPERATIONS_PUB.REPUBLISH_MODEL(

    P_API_VERSION => P_API_VERSION,

    P_PUBLICATION_ID => P_PUBLICATION_ID,

    P_USER_ID => P_USER_ID,

    P_RESP_ID => P_RESP_ID,

    P_APPL_ID => P_APPL_ID,

    P_START_DATE => P_START_DATE,

    P_END_DATE => P_END_DATE,

    X_RUN_ID => X_RUN_ID,

    X_STATUS => X_STATUS

  );

  DBMS_OUTPUT.PUT_LINE('X_RUN_ID = ' || X_RUN_ID);

  DBMS_OUTPUT.PUT_LINE('X_STATUS = ' || X_STATUS);

END;

Enable PLSQL Trace with bindings

Sql commands to enable/disable trace in Oracle plsql session.

ALTER SESSION SET sql_trace = true;

ALTER SESSION SET tracefile_identifier = 'pm_test1trace';

--enable trace with bindings and wait

ALTER SESSION set events '10046 trace name context forever, level 12';

--set of plsql blocks to be traced

select * from cz_devl_projects where name like 'PM 10';

ALTER SESSION SET sql_trace = false;

select * from v$parameter where name like '%user%dest%';

Validate Existing OAF Session in JSP/Servlet

One way to validate OAF session in custom jsp/servlet in Oracle applications. Method will return "INVALID" if the current session is invalid, "EXPIRED" if current session is expired and "VALID" if current session is valid.
You can also create new session if current session is invalid, wiill post detail in some other post. But, usually if Functionality is part of existing oracle application module, then you will just want to validate if current session is valid or not. If not, you may want to redirect to /OA_HTML/AppsLocalLogin.jsp page.

public static String validateOAFSession(HttpServletRequest request, HttpServletResponse response) {
    WebAppsContext appsContext = null;
    try {
        appsContext = WebRequestUtil.createWebAppsContext(request, response);
        if (appsContext != null) {
            String sessionCookie = WebRequestUtil.getSessionCookie(request, response, appsContext);
            if (sessionCookie == null) {
                return "INVALID";
            }
            if (!appsContext.validateSession(sessionCookie, true)) {
                return "EXPIRED";
            }
        }
    } catch (IOException ioe) {
        return "INVALID";
    } finally {
        if (appsContext != null) {
            appsContext.freeWebAppsContext();
        }
    }
    return "VALID";
}

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) ;
 

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>

Configurator Model Size - Node count of the model

Usually its a common question in Configurator Development like how large the model is. Model size is usually determined by number of nodes created under it and its referenced models. When model performance is raised as a question, first question asked by analyzer would be how big the model is. Here is the query you can use for the same. This query returns the node count of all the models which are under given folder. You can modify the query to get node count for given project.

Q1) Count of nodes in model which are directly created under model

SELECT rpf.name folder_name,

  p.name model_name,

  p.devl_project_id,

  COUNT(*) node_count

FROM cz_devl_projects p,

  cz_ps_nodes pn,

  cz_rp_entries rpp,

  cz_rp_entries rpf

WHERE 1                  =1

AND rpp.object_id        =p.devl_project_id

AND rpp.enclosing_folder = rpf.object_id

AND pn.devl_project_id   = p.devl_project_id

AND p.deleted_flag       =0

AND pn.deleted_flag      =0

AND rpp.object_type      ='PRJ'

AND rpf.object_type      = 'FLD'

  --AND P.DEVL_PROJECT_ID=1037298

AND RPF.NAME LIKE 'Division A Models'

GROUP BY rpf.name ,

  p.name,

  p.devl_project_id;

Q2) Count of nodes in model which are directly created under this model + nodes created in all the referenced models of this model

SELECT rpf.name folder_name,

  p.name model_name,

  p.devl_project_id,

  COUNT(*) node_count

FROM cz_devl_projects p,

  cz_ps_nodes pn,

  cz_rp_entries rpp,

  cz_rp_entries rpf

WHERE 1                  =1

AND rpp.object_id        =p.devl_project_id

AND rpp.enclosing_folder = rpf.object_id

AND pn.devl_project_id  IN

  (SELECT component_id

  FROM cz_model_ref_expls mr

  WHERE mr.model_id  =p.devl_project_id

  AND mr.deleted_flag=0

  )

AND p.deleted_flag  =0

AND pn.deleted_flag =0

AND rpp.object_type ='PRJ'

AND rpf.object_type = 'FLD'

  --AND P.DEVL_PROJECT_ID=1037298

AND RPF.NAME LIKE ' Division A Models'

GROUP BY rpf.name ,

  p.name,

  p.devl_project_id

  --order by node_count desc

  ;

Configurator Table - CZ_SERVERS

Configurator schema has plenty of tables, but one of the important one is CZ_SERVERS. Although you will not need to look at it frequently, but when something is wrong with instance or after cloning/migration, you need to ensure that CZ_SERVERS table contains correct data.

You can get complete details of the columns from Oracle ETRM

Few useful information:
The table must contain one entry atleast for local_name="LOCAL" and server_local_id=0 value. This record identifies local host for the server. If you clone or create new instance, then please ensure that the hostname is updated with this value from that instance
select host_name from v$instance

Please ensure that all the db links are active for the servers which are you using (either for BOM Import or Publication target). The db link names should be available in FNDNAM_LINK_NAME column. Same should exists in dba_db_links table.

IMPORT_ENABLED
Should be set to 1 only for one record in the table.

SOURCE_SERVER_FLAG
This flag will be set to 1 for the server record from which the publication has been done. If Instance A is doing publication to Instance B, then B's cz_servers will contain value "1" for the row which contains entry of Instance A.
If this flag is set to 1 for any record in cz_servers, then you cannot launch Configurator Developer on the instance. The reason is - Configurator thinks that this instance is a publication target. Doing a development activity on the instance may cause data corruption.

Making the Configurator Features Required dynamically

Oracle Configurator provides in built support to make any option feature required at design time itself. If minimum selection of any option feature is set to 1, it becomes required at runtime (Configuration time) and user will not be finish the Configuration with complete status=true.

Sometimes you need to make option feature required at runtime - based on certain conditions (selections).
Lets say you need to make Option Feature OF (min=0) required if Boolean Feature BF is selected. You can quickly write a rule like
BF requires OF
At runtime, if BF is selected by user or by rules, OF will become mandatory selection for user.

What about other type of features? We cannot use Text Feature in rule at all. So, how to make all other features mandatory on certain conditions. Its very standard requirement and Oracle has provided a white paper on it. Please refer paper "Making a Numeric Feature a Required Input in Oracle Configurator" in Oracle Configurator White Papers [ID 434324.1]

Start Configuration from Jdeveloper

I have seen everyone working on Configurator Extensions wondering if we can start Configuration session from local machine Jdeveloper. Well, the answer is Yes. You can! I have done it in past.

Follow these steps:

• Install Jdeveloper.
• You need is DBC file and put it in local machine directory. Refer DBC file path in your java program in System properties - DBCFILE and JTFDBCFILE.
• Refer Configurator Extension Guide to create configuration using CIO from Java program.
• Get all the classes from middletier required by Configurator (fnd classes). You can start with none and as you start getting error, you can create jar for specific packages like oracle.apps.fnd and put it in your jdev. If you want to know class location on server, you can use CZInfo.jsp file. More info on CZInfo.jsp file is here.

Its very useful for debugging purpose, but its very very slow is what I experienced - depending on your model size and how your db responds to your machine. It actually executes plenty of queries (to get model/rule details from database) to create configuration on local machine and load all those classes which makes it very slow.

Using this you can test/debug-step through your Extension code.

Oracle: Find locking session and Kill

Usually you will see some query taking a lot of time and sometime never returns. Well, if it returns ever, you nee d to improve the explain plan of the query.
But if it never returns (usually update query) then it could be a deadlock in the system somewhere. To find out that particular session and query, following query will give more fruitful information for it.

I found if useful for debugging few issues in Oracle Configurator when some page does not return on Apply button click. Well, but in general following queries are not specific to oracle applications, you can use it anywhere to find locking sessions and eventually kill them.

SELECT sq.*, ses.*
FROM   v$sql sq, v$session ses
WHERE  sq.sql_id = ses.sql_id
 and ses.sid IN (SELECT sid
            FROM   v$lock
            WHERE  (id1,id2,TYPE) IN (SELECT id1,
                                             id2,
                                             TYPE
                                         FROM   v$lock
                                         WHERE  request > 0))
;

Find "BLOCKING_SESSION", analyze and kill if required.

select * from v$session where sid=310;


ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;


ALTER SYSTEM KILL SESSION '276,2323' IMMEDIATE;

Assign different property to node and keep the value as it is

Although same can be done by updating property name directly if the old property is not required at all.

Backup table created as

CREATE TABLE CZ_PS_PROP_VALS_BKUP_6MAR12 AS SELECT * FROM CZ_PS_PROP_vALS;

Current count of this property usage of our models in RMT%In%Process folder

SELECT count(*), P.NAME

FROM cz_ps_prop_vals V, CZ_PROPERTIES P

WHERE P.deleted_flag=0

AND V.DELETED_FLAG=0

AND V.PROPERTY_ID = P.PROPERTY_ID

AND P.name like '%OFFERING%'

AND V.ps_node_id   IN

  (SELECT ps_node_id

  FROM cz_ps_nodes

  WHERE deleted_flag=0

  and devl_project_id IN

    (SELECT object_id

    FROM cz_rp_entries

    WHERE enclosing_folder = 136906

    AND deleted_flag       =0

    )

    )

  GROUP BY P.NAME;

Update query executed to update 394 rows

UPDATE CZ_PS_PROP_VALS

SET PROPERTY_ID = 5302 -- MYOFFERING

WHERE PROPERTY_ID = 5522 --YOUROFFERING

AND DELETED_FLAG=0

AND PS_NODE_ID IN (SELECT ps_node_id

  FROM cz_ps_nodes

  WHERE deleted_flag=0

  and devl_project_id IN

    (SELECT object_id

    FROM cz_rp_entries

    WHERE enclosing_folder = 136906

    AND deleted_flag       =0

    )

    );

Stack - push, pop, max operations in constant time

One good question I was asked in one of the interview in my career.
Implement Integer value Stack such a way that all the following operations are constant operation, i.e. O(1).
void push(int n) -> Pushes element n on the stack
int pop() -> Pops element from the top of the stack and return it.
int max() -> Returns maximum of all elements contained in the Stack at that moment.

Isn't it a good Question?

Oracle Configurator CZInfo.jsp page

I have found CZInfo.jsp a very useful page while working with Oracle applications.
It can be accessed on any EBS instance by directly typing following URL (replace your instance host name and port number)
http://cztest.corp.mycompany.com:8001/OA_HTML/CZInfo.jsp
You will be able to see output of the page only when FND: Diagnostics (profile option) are enabled.

This page will list following:

• System Properties
• It will list all Java System Properties. Many times useful to check if any changes in jserv.properties (11.5.10) or in oc4j.properties (R12) have taken effect or not and to observe many other things.
• You can observe java classpath, few environment variables, and DBC file location. Explore this more to find what more you can do :)
• Publication Lookup
• Useful to do publication lookup for given inventory_item_id/organization_id or product_key.
• It will give publication_id, published model_id and ui_def_id.
• JRAD documents for a given UI
• If you have ever wondered that how XML pages of Configurator UI page (developed in Configurator Developer) are structured or what is stored in MDS repository, you can use this feature to find its details.
• Enter the ui_def_id of the UI. It will display pages and templates used by the UI. You can get ui_def_id from cz_ui_defs table.
• Sometimes very useful for debugging UI corruption issue.
• JRAD documents by Path
• Here you can specify any JRAD document for Oracle application and can see its XML structure. 
• Sometimes useful to find personalization of the page and other details.
• ARCS version of class loaded in the JVM
• This is the most useful feature.
• You can find any java class located on the system (in classpath). It will display the location on the class in middle-tier.
• Sometimes your CX archive class may be present in file system and due to that, you will not be able to see changes from your archive uploaded in Configurator developer. You can check if the same class is there on the file system.
• It will also list arcs (Oracle source control) version of the file. Many times useful and asked by Oracle Support to check the version of specific class to validate if the new fix has been applied properly on the instance.

Adobe Reader PDF launch performance

Recently I have observed that whenever I open any PDF file in Adobe Reader (Version 10.1), it takes lot of time to display PDF file.
Looks like it does some processing of document for loading plugins. I just renamed the plugin's folder under installation directory of Adobe (typically it is C:\Program Files\Adobe\Reader 10.0).
I found two folders for plugin in my machine.
   plug_ins3d
   plug_ins
Just renamed them to some other name.
Now the Adobe is launching PDF very quickly.

Now you will face problem while searching in pdf file - because you have disabled all plugins :(
So now selectively add plugin as you need by creating above folder and finding plugins from renamed folder. They are self explanatory by name, so should not be difficult to find out their usage.