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

check model bill similarity check in bulk

---------Block to submit BOM SIM and publication concurrent reqst-----------------
DECLARE
  CURSOR BOMSIM_PUBLCTN_CURSOR
  IS
    SELECT pn.name model_name,
      rp.name Description,
      rp.enclosing_folder folder_id,
      pn.devl_project_id model_id
    FROM cz_ps_nodes pn,
      cz_rp_entries rp
    WHERE pn.ps_node_id = rp.object_id
    AND rp.deleted_flag = 0
    AND rp.object_type  = 'PRJ'
    AND rp.name NOT LIKE 'Copy%'
    AND (pn.name        like 'MODEL-%')
    AND pn.deleted_flag = 0
    order by pn.name;
  V_inputdata BOMSIM_PUBLCTN_CURSOR%rowtype;
  ln_request_id NUMBER;
BEGIN
  -- Initialize Apps
  fnd_global.apps_initialize (1848 ,22687 ,708 );
  OPEN BOMSIM_PUBLCTN_CURSOR;
  FETCH BOMSIM_PUBLCTN_CURSOR INTO V_inputdata;
  WHILE BOMSIM_PUBLCTN_CURSOR%FOUND LOOP
    --DBMS_OUTPUT.PUT ('Model name '||V_inputdata.Model_Name);
    --DBMS_OUTPUT.PUT (', Folder '||V_inputdata.Folder_ID);
    --DBMS_OUTPUT.PUT_LINE (', Model ID is '||V_inputdata.Model_ID);
    ln_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
    'PROD',                                         -- IN VARCHAR2 DEFAULT CHR (0),          -- argument1
    V_inputdata.Folder_ID,                                          -- IN VARCHAR2 DEFAULT CHR (0),          -- argument2
    V_inputdata.Model_ID,                                -- IN VARCHAR2 DEFAULT CHR (0),                  -- argument3
    CHR (0)                                            -- represents end of arguments
    );
    COMMIT;
    dbms_output.put_line ('l' ||ln_request_id || '.req' || ',' || V_inputdata.Model_Name || ',' || V_inputdata.Description ||',' ||
      V_inputdata.Folder_ID || ',' ||
      V_inputdata.Model_ID ||',' || ln_request_id );
    IF ln_request_id = 0 THEN
      dbms_output.put_line ('Concurrent request failed to submit');
    END IF;
    FETCH BOMSIM_PUBLCTN_CURSOR into V_inputdata;
  END LOOP;
  END;
  ------------------------------------------------------------------------------------------

Oracle Configurator CIO and dependent jar files

Here are the some of the jar files required to build Configurator CX extensions and few other test cases around that.

It contains old com.sun.java.util.collections jar along with JDBC, FND, OC4J and other related jars. Its link to dropbox. These jars are from Oracle applications 12.1.x version.

Although these files are here for reference purpose, its always good to get them from your instance where you are going to deploy your changes. You can get all of the following classes from $JAVA_TOP directory on instance middle-tier.

UPDATE: Link to jar file is removed now. Jar file can be created with following commands from middletier unix box.

bash>cd $JAVA_TOP
bash>jar cvf oracle.apps.cz.jar oracle/apps/cz
bash>jar cvf oracle.apps.fnd.jar oracle/apps/fnd

Update Java Extension ClassName for Configurator CX rules using query

Following query will update the java classname (from xxcz.cx.MyOldClass to xxcz.cx.MyNewClass ) for all the models which are under repository folder name 'Models - In Process'

UPDATE cz_rules ru
SET ru.class_name        = 'xxcz.cx.MyNewClass'
WHERE exists(SELECT
  rpf.name folder_name,
  p.name model_name,
  p.devl_project_id,
  r.name rule_name,
  r.rule_id,
  r.class_name
FROM cz_rules r,
  cz_devl_projects p,
  cz_rp_entries rpp,
  cz_rp_entries rpf
WHERE r.devl_project_id=p.devl_project_id
AND r.rule_type = 300 -- CX rules
AND rpp.object_id       =p.devl_project_id
and rpp.enclosing_folder = rpf.object_id
AND rpp.object_type     ='PRJ'
AND R.DISABLED_FLAG=0  --update only enabled rules
AND rpf.object_type = 'FLD'
--AND P.DEVL_PROJECT_ID=1037298 --add this if you want to update specific model only
AND class_name LIKE 'xxcz.cx.MyOldClass'
AND RPF.NAME LIKE 'Models – In Process'
AND r.rule_id=ru.rule_id
);