The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Help with pl/sql step

Highlighted
Cris Robin
Regular Collector

Help with pl/sql step

i used a pl/sql execution step like this to create a referance:
KCRT_REQUEST_UTIL.create_references
(p_source_entity_id => 20
,p_source_id => [REQ.REQUEST_ID]
,p_target_type_code => 20
,p_target_id => 33583
,p_user_id => 1
,p_ref_relationship_id => 15
,p_parameter_set_context_id => 630)

and it is working like a charm.
but i need to put insted of the 33583 this:
(select rid from (select request_id rid, parameter2 p2, parameter3 year from kcrt_requests_v A where request_type_id=30280 and parameter2='30187' and status_code='IN_PROGRESS' and a.status_id=30200 order by parameter3) where rownum=1)

Please advice

Sagi
2 REPLIES
Erik Cole
Honored Contributor

Re: Help with pl/sql step

Your best bet is probably to pre-determine p_target_id using a request type rule or another execution and store it in a field. Then you can just use that token in your call like you're doing for p_source_id.
Milind Kapdoska
Occasional Contributor

Re: Help with pl/sql step

you can write a wrapper PL/SQL function code and use that. Mentioned below is an exmaple of the same.
function XYZ ......... returns varchar
v_message varchar2(2000);
v_target_id number;
begin

select rid into v_target_id from (select request_id rid, parameter2 p2, parameter3 year from kcrt_requests_v A where request_type_id=30280 and parameter2='30187' and status_code='IN_PROGRESS' and a.status_id=30200 order by parameter3) where rownum=1;

v_message := KCRT_REQUEST_UTIL.create_references
(p_source_entity_id => 20
,p_source_id => [REQ.REQUEST_ID]
,p_target_type_code => 20
,p_target_id => 33583
,p_user_id => 1
,p_ref_relationship_id => 15
,p_parameter_set_context_id => 630);

return v_message;

end;
//Add this to "OnDomLoad" event