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

Change fields on a request from a project using a trigger

Highlighted
mvillalobos
Regular Collector

Change fields on a request from a project using a trigger

Hello
I am trying to modificate some fields from a Project to a request and backwards.
To do it, I thougt the best way is to create a trigger that changes the fields when one of them change.
I am trying to modificate the email field in a request when I modificate it in a project.

I am using:
"CREATE OR REPLACE TRIGGER ppmuser.correo
AFTER UPDATE OF PARAMETER26 ON ppmuser.KCRT_REQ_HEADER_DETAILS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
UPDATE PPMUSER.KCRT_REQ_HEADER_DETAILS
SET PARAMETER26 = :NEW.PARAMETER26
WHERE PARAMETER4 = :NEW.PARAMETER4 and REQUEST_ID <> NEW.REQUEST_ID;
END"


But it fails

Have you done something similar?

Thank you very much
3 REPLIES
Celil
Esteemed Contributor

Re: Change fields on a request from a project using a trigger

Hi,

You said in code when new requestID not same with old one do .... But it is not possible in KCRT_REQ_HEADER_DETAILS table. This field the key field.

Could you please write the error down here?

Celil
Celil

IT Governance Professional
& PPM Solution Architect
Shilpesh Pathak
Acclaimed Contributor

Re: Change fields on a request from a project using a trigger

Hi,

Is your Project and request related to each other in a Parent child relationship? If yes then you will find a record in knta_references table and then you can create a PL/SQL code and use it in a Workflow execution step whenever a decision is taken on the Project/Request.
Before this you might want to check at which stage of Project/Request lifecycle these fields will be modified.
Please let me know if this helps.

Regards,
Shilpesh
mvillalobos
Regular Collector

Re: Change fields on a request from a project using a trigger

Hello Shilpesh Pathak

By the moment i am usin this in a workflow execution step:

The call:
ksc_local_exec sqlplus.exe SYSTEM/PASSWORD@PPM8 @C:\Modifica-campos.sql ('[REQ.VP.CODIGO_PROYECTO]') ('[REQ.VP.CORREO_ELECTRONICO]') ('[REQ.VP.TELEFONO]') ('[REQ.VP.CODIGO_CLIENTE]') ('[REQ.VP.PLAZO_VALIDEZ_PROPUESTA]') ('[REQ.VP.PLAZO_PROPUESTA]') ('[REQ.REQUEST_ID]') ('[REQ.DESCRIPTION]')

The script:
"DEFINE proyecto = &1;
DEFINE correo = &2;
DEFINE telefono = &3;
DEFINE Codigo = &4;
DEFINE validez = &5;
DEFINE elaboracion = &6;
DEFINE solicitud = &7;

UPDATE PPMUSER.KCRT_REQ_HEADER_DETAILS SET PARAMETER26 = &correo, VISIBLE_PARAMETER26 = &correo, PARAMETER25 = &telefono, VISIBLE_PARAMETER25 = &telefono, PARAMETER5 = &codigo, VISIBLE_PARAMETER5 = &codigo, PARAMETER13 = &validez, VISIBLE_PARAMETER13 = &validez, PARAMETER14 = &elaboracion, VISIBLE_PARAMETER14 = &elaboracion, LAST_UPDATE_DATE = (SELECT SYSDATE FROM DUAL), LAST_UPDATED_BY =1
WHERE PARAMETER4 = &proyecto AND REQUEST_ID < &solicitud;

UPDATE PPMUSER.KCRT_REQUEST_DETAILS SET LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = 1
WHERE REQUEST_Id = (SELECT REQUEST_ID FROM PPMUSER.KCRT_REQ_HEADER_DETAILS
WHERE PARAMETER4 = &proyecto AND REQUEST_ID < &solicitud);

UPDATE PPMUSER.KCRT_REQUESTS SET CONTACT_ID = (SELECT CONTACT_ID FROM PPMUSER.KCRT_REQUESTS WHERE REQUEST_ID = &solicitud), DESCRIPTION = (SELECT DESCRIPTION FROM PPMUSER.KCRT_REQUESTS WHERE REQUEST_ID = &solicitud), LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = 1WHERE REQUEST_ID = (SELECT REQUEST_ID FROM PPMUSER.KCRT_REQ_HEADER_DETAILS
WHERE PARAMETER4 = &proyecto AND REQUEST_ID < &solicitud);

COMMIT;
EXIT;
EXIT;"

It works propertly but i think it would be cleaner with a trigger, because the workflow needs a lot of transitions with this way.

With the Trigger the user would only have to press "save" button, and with this way the user has to transition to other state.

Thank you
//Add this to "OnDomLoad" event