Project and Portfolio Management Practitioners Forum
cancel

how create function insert in PPM

Highlighted
KeanChen
Trusted Contributor.

how create function insert in PPM

Hi,

 

 

I need information about create or execute some insert or update in PPM in the worbench.

 

Please Help.

 

Best Regards.

4 REPLIES
Celil
Acclaimed Contributor.

Re: how create function insert in PPM

Hi Kean,

 

You can run any executable thing or PL/SQL from workflow execution step or request type command tab (you know)

 

There is three way:

1) Call the function, which contain your update/insert step.

ex. Function_name('[TOKEN_NAME]')

 

2) Can use SQL type execution step and can call the function like

ex. SELECT FUNCTION_NAME('[TOKEN_NAME]') FROM DUAL

 

In both case you need to define your code inside any Oracle function or package.

 

3) Use an execution step to build a script with 'ksc_begin_script', then execute it with 'ksc_local_exec sqlplus ...'

 

Here is an example update code for oracle:

CREATE OR REPLACE
FUNCTION UPDATE_WORK_TITLE(title IN VARCHAR2, vtitle IN VARCHAR2, request_number IN NUMBER) RETURN VARCHAR2 IS
BEGIN

     UPDATE kcrt_req_header_details
     SET parameter21= title, visible_parameter21=vtitle
     WHERE request_id=request_number;

RETURN NULL;
END UPDATE_WORK_TITLE;

 

PS: If the post resolves your issue, please click KUDOS star icon and mark it as Accepted Solution.

Celil

IT Governance Professional
& PPM Solution Architect
AlexSavencu
Acclaimed Contributor.

Re: how create function insert in PPM

Hi, Kean,

 

adding to Celil's post: you need to have direct access to the PPM schema in order to create such functions / procedures. This is not possible via the PPM workbench.

 

cheers

alex


--remember to kudos people who helped solve your problem
KeanChen
Trusted Contributor.

Re: how create function insert in PPM

Hi,

Thanks for your help, can you explain the execution of ksc_local_exec and ksc_begin_script? Because the procedure doesn't run how Celil shows.

Best Regards.
Mohit_Agrawal
Outstanding Contributor.

Re: how create function insert in PPM

Hi Kean,

 

If you want to update or insert through function then ..

 

1) You can call a function in the execution step and pass the token of that field as a paramter to the function. You need to define this function in the databse schema that you are using.

 

2) You can use SELECT in the below manner, as suggested by Cecil.

SELECT FUNCTION_NAME('[TOKEN_NAME]') FROM DUAL

 

You can use as shown in the screen shot attached. It could be one scenario when you just need to update some values according to the passed parameter in the function.

 

Thanks!!

Mohit Agrawal