Monday, June 04, 2007

How do you open workbench as an application

It (batch file: SVSBLAPP01_2592_3388_60wb_scylla.zip) contains the following lines:
---
REM @ECHO OFF
REM
REM Change to your client install directory.
REM
cd /D D:\Work\ITG\WorkbenchFiles

\Workbench60Scylla
set classpath=.
set classpath=%classpath%;.\knta_classes.jar
set classpath=%classpath%;.\libraries.jar
REM set classpath=%classpath%;.\rmi.zip
REM
REM Change to the host and RMI port of your primary Mercury ITG Server.
REM
REM jview /p /cp %CLASSPATH% com.kintana.core.gui.LogonApplet your_company.domain.com:1200
REM java com.kintana.core.gui.LogonApplet lime.merc-int.com:20124
"C:\Program Files\Java\j2re1.4.2_06\bin\java" com.kintana.core.gui.LogonApplet scylla:10001
---

Change the line starting with cd to point to a directory where you will place the batch file.
Change the last line, to contain the path to your Java installation.
Change the last part of the last line, i.e. scylla:10001. Replace scylla with the host name of your server machine, and the 10001 with the RMI port used for your instance. If you're not sure about the RMI port, check the RMI_URL entry in the server.conf or open the workbench the normal way, i.e. via the dashboard, and you will see the port number in the top bar.

You also have to copy over the files knta_classes.jar and libraries.jar to the same directory where you place the batch file.
You can find the two jar files on the server in the following directory \server\kintana\deploy\itg.war\WEB-INF\lib

Note: every time you apply a service pack to your ITG instance you will have to copy over the to jar files again.

Thursday, March 01, 2007

Can any one explain me what is the use of PARAMETER_SET_CONTEXT_ID

Answer1
Each parameter (field) in ITG has a unique identifier. PARAMETER_SET_CONTEXT_ID is used to identified each field in the request. So, when using rules, you can make sure of a parameter using its individual identifier (PARAMETER_SET_CONTEXT_ID). It can be used for several other reasons.

Now, I use this PARAMETER_SET_CONTEXT_ID mostly when using a Table Component. You can refer to PARAMETER_SET_FIELD_ID too, it is very similar. I hope this asnwers your question. Let me know if you want something more specific.

Answer2
PARAMETER_SET_CONTEXT_ID is a primary key from the table KNTA_PARAMETER_SET_CONTEXTS, it is linked to table KNTA_PARAMETER_SETS using PARAMETER_SET_ID. PARAMETER_SET_ID indicates whether the data item is "Request Header Fields", "Workflow Commands " etc.
Based on PARAMETER_SET_CONTEXT_ID , you can find,say, field information for a req type from KCRT_REQUEST_TYPE_FIELDS_V, which stores field name, parameter no, token its validation etc.You can find more info from the Technical Ref Manual from Mercury.

Wednesday, February 28, 2007

How to find recent login attempts?

select username, count(*)
from knta_logon_attempts
where creation_date > sysdate - 1
group by username
order by username

Tuesday, February 27, 2007

WF Creation of a Project

Purpose: This solution creates a ITG Demand Management workflow step that, given a project template and project name, can be used to create a ITG Drive Project and tie that Project to the Request. The solution will also optionally copy information from the Request (header or Detail fields) to the Project custom fields and Project User Data by matching tokens based on token name.


Installation:


FIRSTLY – go into sqlplus, i.e., sqlplus fasttour/fasttour@mitg, then type @cust_project_creation.sql. There should be no errors, if there is one about ‘invalid’ then try running the script as a different sql user, as your user doesn’t have the appropriate privileges.

1) Run the package CUST_PROJECT_CREATION.sql against the database. You do not need to bounce the ITG server. This custom package contains the function called by the workflow step to create the Project. It leverages the KDRV_PROJECT_CP package to perform the bulk of the Project creation but has custom code to create the references, copy data from the Request, and perform some clean-up since the KDRV_PROJECT_CP package is meant to create a Project from another Project, not a Project template.

2) Create a ITG Demand Management workflow step of type PL/SQL that calls the appropriate custom function. A sample is below:

















Some key points:
· Using P_COPY_MATCHING_TOKENS, you can determine whether to copy data from the Request to the Project. The copy will look at the token names in the project custom fields and project user data and will look for matches in the request detail or the request header (even if the validations or prompts are different). Note that the copy function is a one-time function. If the data on the Request changes at a later date, it is not propagated to the Project.
· Using P_CREATE_REFERENCE, you can determine whether to create a reference. Note that this reference will be of type ‘Related to this Request’ and will not stop the Request from processing. You will need to Refresh the Request to see the reference on the function completes.
· P_MANAGER_ID can be NULL, in which case the Project is created with no project manager
· If you are not capturing a explicit Project Name in the Request, you can use something like ‘Project Created From Request: [REQ.REQUEST_ID]’ instead of ‘[REQD.PROJECT_NAME]’
· You will need to pass in a Project Template ID into the function. This will normally be a request detail field where the user explicitly picks a Template using the ‘DRV - Project Template Names – Enabled’ validation. Or if might be a field that is automatically derived (using Request Type rules) from some other information on the Request. If the function succeeds, it will return ‘SUCCESS’, which matches the ‘Success’ result. On failure it gives the actual error message. This means that creating transition for failures will not have any effect on workflow transition.. However, you can use the ‘Other Results’ to capture these results is you do want to send a notification or transition out. See example below:

The content of the script "cust_project_creation60.sql" is:
CREATE OR REPLACEPACKAGE cust_project_creation AS
---- This version of the package is compatible with Kintana 6.0.--
FUNCTION WF_PROJECT_CREATE(P_TEMPLATE_ID IN NUMBER, P_PROJECT_NAME IN VARCHAR2, P_MANAGER_ID IN VARCHAR2, P_USER_ID IN NUMBER, P_USR_DBG IN NUMBER DEFAULT KNTA_Constant.Debug_None, P_REQUEST_ID IN NUMBER, P_CREATE_REFERENCE IN VARCHAR2 DEFAULT 'Y', P_COPY_MATCHING_TOKENS IN VARCHAR2 DEFAULT 'Y') RETURN VARCHAR2;
PROCEDURE PROJECT_FROM_TEMPLATE(P_TEMPLATE_ID IN NUMBER, P_PROJECT_NAME IN VARCHAR2, P_MANAGER_ID IN VARCHAR2, P_USER_ID IN NUMBER, P_USR_DBG IN NUMBER DEFAULT KNTA_Constant.Debug_None, O_NEW_PROJECT_ID OUT NUMBER, O_LAST_UPDATE_DATE OUT DATE, O_ENTITY_LAST_UPDATE_DATE OUT DATE, O_MESSAGE_TYPE OUT NUMBER, O_MESSAGE_NAME OUT VARCHAR2, O_MESSAGE OUT VARCHAR2);
END;/
CREATE OR REPLACEPACKAGE BODY cust_project_creation AS
l_stmt_num NUMBER;
FUNCTION wf_project_create(p_template_id IN NUMBER, p_project_name IN VARCHAR2, p_manager_id IN VARCHAR2, p_user_id IN NUMBER, p_usr_dbg IN NUMBER DEFAULT KNTA_Constant.Debug_None, p_request_id IN NUMBER, p_create_reference IN VARCHAR2 DEFAULT 'Y', p_copy_matching_tokens IN VARCHAR2 DEFAULT 'Y') RETURN VARCHAR2 IS
l_new_project_id NUMBER;l_last_update_date DATE;l_entity_last_update_date DATE;l_message_type NUMBER;l_message_name VARCHAR2(200);l_message VARCHAR2(400);
l_ref_1 NUMBER;l_ref_2 NUMBER;
l_project_token VARCHAR2(100);l_project_param_num NUMBER;l_request_batch_num NUMBER;l_request_param_num NUMBER;l_request_header_batch_num NUMBER;l_request_header_param_num NUMBER;
l_header_field VARCHAR2(50);l_header_visible_field VARCHAR2(50);
CURSOR get_project_tokens ISSELECT psf.parameter_set_field_id, psf.parameter_token, psf.parameter_column_numberFROM knta_parameter_set_fields psf, knta_parameter_set_contexts pscWHERE psc.parameter_set_id = 901 /* Project Detail Parameters */AND psc.context_value = to_char(p_template_id)AND psf.parameter_set_context_id = psc.parameter_set_context_idORDER BY psf.parameter_column_number;
CURSOR get_project_ud ISSELECT psf.parameter_set_field_id, psf.parameter_token, psf.parameter_column_numberFROM knta_parameter_set_fields psf, knta_parameter_set_contexts pscWHERE psc.parameter_set_id = 900 -- Project User DataAND psf.parameter_set_context_id = psc.parameter_set_context_idORDER BY psf.parameter_column_number;
l_sql_statement varchar2(20000) := NULL;l_cursor number;l_row_count number;
BEGIN
SAVEPOINT A;
l_stmt_num := 10;
PROJECT_FROM_TEMPLATE(p_template_id => p_template_id, p_project_name => p_project_name, p_manager_id => p_manager_id, p_user_id => p_user_id, p_usr_dbg => p_usr_dbg, o_new_project_id => l_new_project_id, o_last_update_date => l_last_update_date, o_entity_last_update_date => l_entity_last_update_date, o_message_type => l_message_type, o_message_name => l_message_name, o_message => l_message);
IF (l_message_type != KNTA_Constant.SUCCESS) THEN ROLLBACK to SAVEPOINT A; RETURN(l_message);END IF;
IF (p_create_reference = 'Y' AND p_request_id IS NOT NULL) THEN
l_stmt_num := 20;
SELECT knta_references_s.NEXTVAL INTO l_ref_1 FROM sys.dual;
l_stmt_num := 30;
SELECT knta_references_s.NEXTVAL INTO l_ref_2 FROM sys.dual;
l_stmt_num := 40;
INSERT INTO knta_references (reference_id , created_by , creation_date , last_updated_by , last_update_date , target_type_code , ref_relationship_id , source_entity_id , source_id , original_source_id , reverse_reference_id , enabled_flag , parameter_set_context_id , parameter1) VALUES (l_ref_1, p_user_id, SYSDATE, p_user_id, SYSDATE, 6, -- Project 400, -- Related to this Request 20, -- Request p_request_id, p_request_id, l_ref_2, 'Y', 650, l_new_project_id);
l_stmt_num := 50;
INSERT INTO knta_references (reference_id , created_by , creation_date , last_updated_by , last_update_date , target_type_code , ref_relationship_id , source_entity_id , source_id , original_source_id , reverse_reference_id , enabled_flag , parameter_set_context_id , PARAMETER1) VALUES (l_ref_2, p_user_id, SYSDATE, p_user_id, SYSDATE, 20, -- Request 403, -- Related to this Project 6, -- Project l_new_project_id, l_new_project_id, l_ref_1, 'Y', 650, p_request_id);
END IF;
l_stmt_num := 100;
IF (p_copy_matching_tokens = 'Y' AND p_request_id IS NOT NULL) THEN
-- Open cursor used by dynamic sql l_stmt_num := 105; l_cursor := dbms_sql.open_cursor;
-- Fill in the custom fields for matching tokens from the request -- This will only fill in custom fields for the project detail fields -- defined at the master project level. (The project template may have -- custom fields defined at the sub-project level.)
l_stmt_num := 110;
FOR gpt IN get_project_tokens LOOP
-- Look for a match in the request detail fields
l_stmt_num := 120;
SELECT NVL(MAX(psf.batch_number),0), NVL(MAX(psf.parameter_column_number),0) INTO l_request_batch_num, l_request_param_num FROM knta_parameter_set_fields psf, knta_parameter_set_contexts psc, kcrt_requests req WHERE req.request_id = p_request_id AND psc.parameter_set_id = 213 -- Request Detail Parameters AND psc.context_value = to_char(req.request_type_id) AND psf.parameter_set_context_id = psc.parameter_set_context_id AND psf.parameter_token = gpt.parameter_token; -- Request Token matches Project Token
IF (l_request_param_num > 0) THEN l_stmt_num := 130;
l_sql_statement := 'UPDATE kdrv_project_details SET (parameter' TO_CHAR(gpt.parameter_column_number) ', visible_parameter' TO_CHAR(gpt.parameter_column_number) ') = (SELECT parameter' TO_CHAR(l_request_param_num) ', visible_parameter' TO_CHAR(l_request_param_num) ' FROM kcrt_request_details rd, kcrt_requests req WHERE req.request_id = ' TO_CHAR(P_REQUEST_ID) ' AND rd.request_type_id = req.request_type_id AND rd.request_id = req.request_id AND rd.batch_number = ' TO_CHAR(l_request_batch_num) ') WHERE project_id = ' TO_CHAR(l_new_project_id);
l_stmt_num := 140; dbms_sql.parse(l_cursor, l_sql_statement,1);
l_stmt_num := 150; l_row_count := dbms_sql.execute(l_cursor);
ELSE -- Check to see if the token matches a request header token
l_stmt_num := 160;
SELECT MAX(decode(psf.parameter_token, 'CONTACT_EMAIL','CONTACT_EMAIL_ID', 'CONTACT_NAME','CONTACT_NAME_ID', 'CONTACT_PHONE_NUMBER','CONTACT_PHONE_NUMBER_ID', 'APPLICATION_CODE','APPLICATION', 'REQUEST_GROUP_CODE','PROJECT_CODE', psf.parameter_token)), MAX(decode(psf.parameter_token, 'APPLICATION_CODE','APPLICATION_MEANING', 'ASSIGNED_TO_GROUP_ID','ASSIGNED_TO_GROUPNAME', 'ASSIGNED_TO_USER_ID','ASSIGNED_TO_USERNAME', 'COMPANY','COMPANY_NAME', 'CREATED_BY','CREATED_BY_USERNAME', 'DEPARTMENT_CODE','DEPARTMENT_MEANING', 'PRIORITY_CODE','PRIORITY_MEANING', 'REQUEST_GROUP_CODE','PROJECT_NAME', 'REQUEST_SUB_TYPE_ID','REQUEST_SUBTYPE_NAME', 'REQUEST_TYPE_ID','REQUEST_TYPE_NAME', 'STATUS_ID','STATUS_NAME', 'WORKFLOW_ID','WORKFLOW_NAME', psf.parameter_token)), NVL(MAX(psf.batch_number),0), NVL(MAX(psf.parameter_column_number),0) INTO l_header_field, l_header_visible_field, l_request_header_batch_num, l_request_header_param_num FROM knta_parameter_set_fields psf, knta_parameter_set_contexts psc, kcrt_request_types rt, kcrt_requests req WHERE req.request_id = p_request_id AND rt.request_type_id = req.request_type_id AND psc.parameter_set_id = 217 -- Request Header Types AND psc.context_value = to_char(rt.request_header_type_id) AND psf.parameter_set_context_id = psc.parameter_set_context_id AND psf.parameter_token = gpt.parameter_token; -- Request Header Token matches Project Token
IF (l_header_field IS NOT NULL AND l_request_header_batch_num = 0) THEN l_stmt_num := 170;
l_sql_statement := 'UPDATE kdrv_project_details SET (parameter' TO_CHAR(gpt.parameter_column_number) ', visible_parameter' TO_CHAR(gpt.parameter_column_number) ') = (SELECT ' l_header_field ', ' l_header_visible_field ' FROM kcrt_requests_v req WHERE req.request_id = ' TO_CHAR(P_REQUEST_ID) ' AND req.batch_number = 1 ) WHERE project_id = ' TO_CHAR(l_new_project_id); l_stmt_num := 180; dbms_sql.parse(l_cursor, l_sql_statement,1);
l_stmt_num := 190; l_row_count := dbms_sql.execute(l_cursor);
ELSE l_stmt_num := 191;
l_sql_statement := 'UPDATE kdrv_project_details SET (parameter' TO_CHAR(gpt.parameter_column_number) ', visible_parameter' TO_CHAR(gpt.parameter_column_number) ') = (SELECT parameter' l_request_header_param_num ', visible_parameter' l_request_header_param_num ' FROM kcrt_req_header_details krhd WHERE krhd.request_id = ' TO_CHAR(P_REQUEST_ID) ' AND krhd.batch_number = ' l_request_header_batch_num ' ) WHERE project_id = ' TO_CHAR(l_new_project_id); l_stmt_num := 192; dbms_sql.parse(l_cursor, l_sql_statement,1);
l_stmt_num := 193; l_row_count := dbms_sql.execute(l_cursor); END IF;
END IF;
END LOOP; -- For gpt IN get_project_tokens LOOP
-- Fill in the project user data for matching tokens from the request
l_stmt_num := 210;
FOR gpu IN get_project_ud LOOP
-- Look for a match in the request detail fields
l_stmt_num := 220;
SELECT NVL(MAX(psf.batch_number),0), NVL(MAX(psf.parameter_column_number),0) INTO l_request_batch_num, l_request_param_num FROM knta_parameter_set_fields psf, knta_parameter_set_contexts psc, kcrt_requests req WHERE req.request_id = p_request_id AND psc.parameter_set_id = 213 -- Request Detail Parameters AND psc.context_value = TO_CHAR(req.request_type_id) AND psf.parameter_set_context_id = psc.parameter_set_context_id AND psf.parameter_token = gpu.parameter_token; -- Match Request Detail Token with Project User Data Token
IF (l_request_param_num > 0) THEN l_stmt_num := 230; l_sql_statement := 'UPDATE kdrv_projects SET (user_data' to_char(gpu.parameter_column_number) ', visible_user_data' to_char(gpu.parameter_column_number) ') = (SELECT parameter' to_char(l_request_param_num) ', visible_parameter' to_char(l_request_param_num) ' FROM kcrt_request_details rd, kcrt_requests req WHERE req.request_id = ' to_char(P_REQUEST_ID) ' AND rd.request_type_id = req.request_type_id AND rd.request_id = req.request_id AND rd.batch_number = ' to_char(l_request_batch_num) ') WHERE project_id = ' to_char(l_new_project_id);
l_stmt_num := 240; dbms_sql.parse(l_cursor, l_sql_statement,1);
l_stmt_num := 250; l_row_count := dbms_sql.execute(l_cursor);
ELSE -- Check to see if the token matches a request header token l_stmt_num := 260;
SELECT MAX(DECODE(psf.parameter_token, 'CONTACT_EMAIL','CONTACT_EMAIL_ID', 'CONTACT_NAME','CONTACT_NAME_ID', 'CONTACT_PHONE_NUMBER','CONTACT_PHONE_NUMBER_ID', 'APPLICATION_CODE','APPLICATION', 'REQUEST_GROUP_CODE','PROJECT_CODE', psf.parameter_token)), MAX(decode(psf.parameter_token, 'APPLICATION_CODE','APPLICATION_MEANING', 'ASSIGNED_TO_GROUP_ID','ASSIGNED_TO_GROUPNAME', 'ASSIGNED_TO_USER_ID','ASSIGNED_TO_USERNAME', 'COMPANY','COMPANY_NAME', 'CREATED_BY','CREATED_BY_USERNAME', 'DEPARTMENT_CODE','DEPARTMENT_MEANING', 'PRIORITY_CODE','PRIORITY_MEANING', 'REQUEST_GROUP_CODE','PROJECT_NAME', 'REQUEST_SUB_TYPE_ID','REQUEST_SUBTYPE_NAME', 'REQUEST_TYPE_ID','REQUEST_TYPE_NAME', 'STATUS_ID','STATUS_NAME', 'WORKFLOW_ID','WORKFLOW_NAME', psf.parameter_token)) INTO l_header_field, l_header_visible_field FROM knta_parameter_set_fields psf, knta_parameter_set_contexts psc, kcrt_request_types rt, kcrt_requests req WHERE req.request_id = P_REQUEST_ID AND rt.request_type_id = req.request_type_id AND psc.parameter_set_id = 217 -- Request Header Types AND psc.context_value = to_char(rt.request_header_type_id) AND psf.parameter_set_context_id = psc.parameter_set_context_id AND psf.parameter_token = gpu.parameter_token;
IF (l_header_field IS NOT NULL) THEN
l_stmt_num := 270;
l_sql_statement := 'UPDATE kdrv_projects SET (user_data' to_char(gpu.parameter_column_number) ', visible_user_data' to_char(gpu.parameter_column_number) ') = (SELECT ' l_header_field ', ' l_header_visible_field ' FROM kcrt_requests_v req WHERE req.request_id = ' to_char(P_REQUEST_ID) ' AND req.batch_number = 1 ) WHERE project_id = ' to_char(l_new_project_id);
l_stmt_num := 280; dbms_sql.parse(l_cursor, l_sql_statement,1);
l_stmt_num := 290; l_row_count := dbms_sql.execute(l_cursor);
END IF;
END IF;
END LOOP; -- FOR gpu IN get_project_ud LOOP
dbms_sql.close_cursor (l_cursor);
END IF; -- (P_COPY_MATCHING_TOKENS = 'Y' AND P_REQUEST_ID IS NOT NULL)
l_stmt_num := 1000;
RETURN ('SUCCESS');
EXCEPTION
WHEN OTHERS THEN ROLLBACK to SAVEPOINT A; knta_message.Get_Ora('WF_PROJECT_CREATE', l_stmt_num, l_message_type, l_message_name, l_message); RETURN(l_message);END;
-- ----------------------------------------------------- --
PROCEDURE project_from_template(p_template_id IN NUMBER, p_project_name IN VARCHAR2, p_manager_id IN VARCHAR2, p_user_id IN NUMBER, p_usr_dbg IN NUMBER DEFAULT KNTA_Constant.Debug_None, o_new_project_id OUT NUMBER, o_last_update_date OUT DATE, o_entity_last_update_date OUT DATE, o_message_type OUT NUMBER, o_message_name OUT VARCHAR2, o_message OUT VARCHAR2) IS
l_template_project_id number; l_template_name varchar2(200); l_project_name varchar2(200) := p_project_name; l_region_id NUMBER;
l_capex_opex_enabled_flag VARCHAR2(1);BEGIN l_stmt_num := 2000;
SELECT template_top_project_id, project_template_name INTO l_template_project_id, l_template_name FROM kdrv_project_templates WHERE project_template_id = p_template_id;
l_stmt_num := 2010; SELECT kps.region_id, kps.capex_opex_enabled_flag INTO l_region_id, l_capex_opex_enabled_flag FROM kdrv_project_setups kps, kdrv_project_templates kpt WHERE kpt.project_template_id = p_template_id AND kps.source_id = kpt.template_top_project_id;
l_stmt_num := 2020; kdrv_projects_cp.copy (p_project_id => o_new_project_id, p_old_project_id => l_template_project_id, p_project_name => l_project_name, p_project_manager_id_list => to_number(p_manager_id), p_copy_project_setup => 'Y', p_copy_notifications => 'Y', p_copy_custom_fields => 'Y', p_copy_notes => 'Y', p_copy_action_items => 'Y', p_copy_user_data => 'Y', p_copy_percent_complete => 'Y', p_copy_priority => 'Y', p_copy_actual_dates => 'Y', p_copy_estimated_dates => 'Y', p_copy_references => 'Y', p_cost_cap_enabled => l_capex_opex_enabled_flag, p_region_id => l_region_id, p_user_id => p_user_id, p_usr_dbg => p_usr_dbg, o_last_update_date => o_last_update_date, o_entity_last_update_date => o_entity_last_update_date, o_message_type => o_message_type, o_message_name => o_message_name, o_message => o_message); -- Change copy from a template to a regular project
l_stmt_num := 2030; UPDATE kdrv_projects SET template_flag = 'N' WHERE master_project_id = o_new_project_id;
-- -- Set the project as if it were created manually from a template. (New for 5.0) -- l_stmt_num := 2040; UPDATE kdrv_projects SET created_from_template_flag = 'Y' WHERE project_id = o_new_project_id;
-- -- Enable any template custom fields -- -- Update the PROJECT_TEMPLATE_ID. Note that for some entries the project_template_id -- might already be populated. This occurs when the template was constructed by -- importing other templates for sub-projects. (New for 5.0) -- l_stmt_num := 2050; UPDATE kdrv_projects SET project_template_id = p_template_id WHERE project_id = o_new_project_id AND project_template_id IS NULL;
-- -- If the template has custom fields then set the flag -- l_stmt_num := 2060; UPDATE kdrv_projects p SET has_custom_fields_flag = 'Y' WHERE p.master_project_id = o_new_project_id AND p.project_type_code = 'PROJECT' AND NVL(p.has_custom_fields_flag, 'N') != 'Y' AND EXISTS (SELECT 1 FROM knta_parameter_set_contexts psc, knta_parameter_set_fields psf WHERE psc.parameter_set_id = 901 AND psc.context_value = to_char(p.project_template_id) AND psf.parameter_set_context_id = psc.parameter_set_context_id);
l_stmt_num := 2070; INSERT INTO kdrv_project_details (project_detail_id , creation_date , created_by , last_update_date , last_updated_by , project_id , project_template_id , parameter_set_context_id) SELECT kdrv_project_details_s.nextval, SYSDATE, p_user_id, SYSDATE, p_user_id, p.project_id, p.project_template_id, psc.parameter_set_context_id FROM kdrv_projects p, knta_parameter_set_contexts psc WHERE p.master_project_id = o_new_project_id AND p.project_type_code = 'PROJECT' AND p.has_custom_fields_flag = 'Y' AND psc.parameter_set_id = 901 -- Project Detail Parameters AND psc.context_value = TO_CHAR(p.project_template_id) AND NOT EXISTS (SELECT 'Detail already exists' FROM kdrv_project_details pd WHERE pd.project_id = p.project_id);
-- -- Set the master project level to have the correct template_id, and context_value -- l_stmt_num := 2080; UPDATE kdrv_project_details SET project_template_id = p_template_id, parameter_set_context_id = (SELECT parameter_set_context_id FROM knta_parameter_set_contexts WHERE parameter_set_id = 901 AND context_value = TO_CHAR(p_template_id)) WHERE project_id = o_new_project_id;END;
END cust_project_creation;/
set arraysize 2;show errors package CUST_PROJECT_CREATION;show errors package body CUST_PROJECT_CREATION;

PM% Complete Calculations

Davey,

Percent(%) Complete for a Project is calculated based on the following:

a) Total number of days scheduled duration.Sum of the number of days of Scheduled Duration of individual Tasks, including Tasks directly under the Project and the Tasks below its sub-projects(to any level).
b) Total number of days of work completed.For example, if a Task is estimated at 50 days of scheduled duration and 10% of it is completed, it is calculated as 5 days worth of work completed on that Task.

Example:
Consider the following Project hierarchy as an example:M1 (master project) -SP1 (sub-project) - 100 % complete ---T1 - duration 1 day - 100 % complete ---T2 - duration 1 day - 100 % complete -SP2 (sub-project) - 2% complete (by computing (7/354) * 100 ) ---T1 - duration 300 days - 0% complete ---T2 - duration 2 days - 100 % complete ---T3 - duration 2 days - 0% complete ---T4 - duration 50 days - 10% complete -SP3 (sub-project) - 0% complete ---T1 - duration 500 days - 0% complete ---T2 - duration 250 days - 0% complete Total duration of work in person-days = 1106Total work completed in person-days = 9 (100% of 1 day of effort + 100% of 1 day of effort + 100% of 2 days of effort + 10% of 50 days of effort)Therefore the total percent complete = ((9/1106)*100) = 0.81, which is rounded off as 0%.

In your example:

Total duration of work = 279.4 days (taken from task level)
Total work completed in person days = 159 + 4.05 = 163.05 days (See below)
Therefore the total % complete = (163.05 / 279.4)*100 = 58.35 = 58 % rounded

100% of 159 days = 159 days (taken from task level!)
27% of 15 days = 4.05 days work completed.

How do you copy from one field to another?


Thursday, January 18, 2007

How to identify users who have already had a dashboard distributed to them?

SELECT (u.first_name ' ' u.last_name)FULL_NAME, u.username USERNAME, u.user_id USER_ID, u.email_address Email, p.title Page_Title
FROM KNTA_USERS u, knta_user_security us, DSH_PAGES p, DSH_USER_PAGES up
WHERE ((u.end_date is NULL) OR (u.end_date > sysdate))
AND u.user_id=us.user_id -- joining the rows
AND up.page_id=p.page_id (+)
AND up.user_id=u.user_id -- joining the rows
AND p.title Page_Title = 'Change'
AND p.title = 'Change'GROUP BY p.title, u.user_id, u.username, u.first_name, u.last_name, u.email_address, p.title
Order by Username

Monday, October 23, 2006

Migration - things to be aware of

If you have made changes to the live system without doing a migration from test to live, then when you migrate a new request/workflow etc across to test and overwrite the validations, you will end up with problems.

Seems obvious ....but easy to do.

CRT-Application-Enabled (On Live)
Contained:
  • Telebet Wireless
  • Telebet R&D
  • Telebet Security

Overwrote test version of this which did not contain this information and immediately created problem for end user using the above applications.

CRT - Priority - Enabled
On Test had:
Code = Normal
Meaning = Normal

On Live, these values had been stored as:
Code = NORMAL
Meaning = Normal

This resulted in "Invalid Priority" being displayed on the dashboard

Corrected in and it was displayed ok

Tuesday, August 29, 2006

How to autopopulate a field based on a transition

create parameter that will capture sysdate
create execution step that will run the sql to generate sysdate
create execution step that will take sysdate and populate a field