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;
