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

Configurator: Check for Model Logic Status using Plsql API

Sometimes its useful to find how many model's logic is not upto date using plsql API. One simple example would be for during republish of the model - if model logic is not upto date at the time of publication processing, publication program will fail and model will not be published. Following block can be handy before running any publication or locally creating configuration for the model using CIO API.

DECLARE

  p_model_id      NUMBER := 1774920;

  x_return_status VARCHAR2(10);

  x_msg_data      VARCHAR2(256);

BEGIN

  CZ_PB_MGR.IS_MODEL_UPTO_DATE( P_MODEL_ID =>p_model_id, X_RETURN_STATUS =>x_return_status, X_MSG_DATA =>x_msg_data);

  dbms_output.put_line('X_RETURN_STATUS = ' || X_RETURN_STATUS);

  dbms_output.put_line('X_MSG_DATA = ' || X_MSG_DATA);

END;

If x_return_status=1 then logic is not upto date, 0 value means logic is upto date.

Debugging Configurator Batch Validation

When you do Book Order in Order Management, it will validate line Configuration using batch validation API. This process causes issues at times and the front end error message simply says like "Configuration validation resulted in errors." without any additional details. As this is background process, it makes debugging difficult.

Here are few important steps to debug Configurator batch validation error:

1) Generate OM debug log for batch validation process. That should reveal some detail about what is happening.

2) Generate sql trace for batch validation process. Useful in certain cases.

3) Check AltBatchValidateUrl parameter from cz_db_settings.

4) Check if above URL is reachable by Database using following script
DECLARE
  config_messages cz_cf_api.CFG_OUTPUT_PIECES ;
  finalURL VARCHAR2(500);
  MESSAGE  VARCHAR2(500);
BEGIN
  dbms_output.put_line('start...');
  finalURL        := 'http://configurator-businessappsLoad.companyname.com:80/configurator/oracle.apps.cz.servlet.UiServlet?test=version';
  config_messages := UTL_HTTP.request_pieces(url => FinalURL);
  dbms_output.put_line('message count = ' || config_messages.COUNT);
  FOR i IN config_messages.FIRST .. config_messages.LAST
  LOOP
    MESSAGE := config_messages(i);
    dbms_output.put_line('Message ' || i || ' : ' || MESSAGE);
  END LOOP;
  dbms_output.put_line('...end');
END;

5) Check if there any additional logs written in Configurator batch validation

select * from cz_db_logs 

where logtime is not null

and loguser = 'batchValidate'

order by logtime desc;

6) Check if you are using model based routing and try disabling it and see if it works.

7) If you are using https url for batch validation, you need check if Wallet setup is done correctly.

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;

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;

PLSQL block for Publication process

If there is any issue entering parameters for Concurrent program or if you want to automate your publishing process, go for following plsql block:

DECLARE

  P_API_VERSION NUMBER;

  P_PUBLICATION_ID NUMBER;

  P_USER_ID NUMBER;

  P_RESP_ID NUMBER;

  P_APPL_ID NUMBER;

  X_RUN_ID NUMBER;

  X_STATUS NUMBER;

BEGIN

  P_API_VERSION := 1.0;

  P_PUBLICATION_ID := 64689;

  P_USER_ID := 1001530;  --EBUSINESS

  P_RESP_ID := 22687;  --Oracle Configurator Administrator

  P_APPL_ID := 708; --Configurator

  CZ_MODELOPERATIONS_PUB.PUBLISH_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,

    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;

Ensure you enable dbms output to check the return status.

Successful submission would return 0 as x_status.

You can also check in Configurator Developer  >  Publication tab for this publication, it should show as Complete.

P.S. All Configurator process APIs are listed in Implementation Guide of Configurator.
Change user_id, resp_id, appl_id as per your requirement.
For errors, refer cz_db_logs table.