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: 

Copy Table Data between tables

SOLVED
Go to solution
Highlighted

Copy Table Data between tables

Hi everyone,

 

I have a workflow with 5 stages, en each stage there are a few tables that are the same through all the stages (they use the same validation). Everytime the users change the stage I need to copy the data of the tables  of the old stage to the new one. I was doing a PLSQL to achieve this (attached to the bottom)

 

But I have troubles trying to figure the PARAMETER_SET_CONTEXT_ID field, due since is a table component I use another  PARAMETER_SET_CONTEXT_ID different than simple fields.

 

How can I obtain the correct context ID? I didn't found the right relation in the data model.

 

Regards

 

Alfredo,

 

PROCEDURE CopiarTablaOD(p_request_id IN NUMBER, p_token_start_table IN varchar2,p_token_end_table IN varchar2,p_reference_code varchar2) as

CURSOR OBTENER_DATOS_TABLA IS
SELECT
CREATED_BY,
LAST_UPDATED_BY,
REQUEST_ID,
SEQ,
PARAMETER1,VISIBLE_PARAMETER1,
PARAMETER2,VISIBLE_PARAMETER2,
PARAMETER3,VISIBLE_PARAMETER3,
PARAMETER4,VISIBLE_PARAMETER4,
PARAMETER5,VISIBLE_PARAMETER5,
PARAMETER6,VISIBLE_PARAMETER6,
PARAMETER7,VISIBLE_PARAMETER7,
PARAMETER8,VISIBLE_PARAMETER8,
PARAMETER9,VISIBLE_PARAMETER9,
PARAMETER10,VISIBLE_PARAMETER10,
PARAMETER11,VISIBLE_PARAMETER11,
PARAMETER12,VISIBLE_PARAMETER12,
PARAMETER13,VISIBLE_PARAMETER13,
PARAMETER14,VISIBLE_PARAMETER14,
PARAMETER15,VISIBLE_PARAMETER15,
PARAMETER16,VISIBLE_PARAMETER16,
PARAMETER17,VISIBLE_PARAMETER17,
PARAMETER18,VISIBLE_PARAMETER18,
PARAMETER19,VISIBLE_PARAMETER19,
PARAMETER20,VISIBLE_PARAMETER20,
PARAMETER21,VISIBLE_PARAMETER21,
PARAMETER22,VISIBLE_PARAMETER22,
PARAMETER23,VISIBLE_PARAMETER23,
PARAMETER24,VISIBLE_PARAMETER24,
PARAMETER25,VISIBLE_PARAMETER25,
PARAMETER26,VISIBLE_PARAMETER26,
PARAMETER27,VISIBLE_PARAMETER27,
PARAMETER28,VISIBLE_PARAMETER28,
PARAMETER29,VISIBLE_PARAMETER29,
PARAMETER30,VISIBLE_PARAMETER30,
PARAMETER31,VISIBLE_PARAMETER31,
PARAMETER32,VISIBLE_PARAMETER32,
PARAMETER33,VISIBLE_PARAMETER33,
PARAMETER34,VISIBLE_PARAMETER34,
PARAMETER35,VISIBLE_PARAMETER35,
PARAMETER36,VISIBLE_PARAMETER36,
PARAMETER37,VISIBLE_PARAMETER37,
PARAMETER38,VISIBLE_PARAMETER38,
PARAMETER39,VISIBLE_PARAMETER39,
PARAMETER40,VISIBLE_PARAMETER40,
PARAMETER41,VISIBLE_PARAMETER41,
PARAMETER42,VISIBLE_PARAMETER42,
PARAMETER43,VISIBLE_PARAMETER43,
PARAMETER44,VISIBLE_PARAMETER44,
PARAMETER45,VISIBLE_PARAMETER45,
PARAMETER46,VISIBLE_PARAMETER46,
PARAMETER47,VISIBLE_PARAMETER47,
PARAMETER48,VISIBLE_PARAMETER48,
PARAMETER49,VISIBLE_PARAMETER49,
PARAMETER50,VISIBLE_PARAMETER50
FROM KCRT_TABLE_ENTRIES TE
WHERE 1=1
AND TE.REQUEST_ID=p_request_id
AND TE.PARAMETER_SET_FIELD_ID=(SELECT PSF.PARAMETER_SET_FIELD_ID FROM KNTA_PARAMETER_SET_FIELDS PSF WHERE SUBSTR(PSF.REFERENCE_CODE,0,LENGTH(p_reference_code))=p_reference_code AND PSF.PARAMETER_TOKEN= p_token_start_table)
AND TE.PARAMETER_SET_CONTEXT_ID=(SELECT PSF.PARAMETER_SET_CONTEXT_ID FROM KNTA_PARAMETER_SET_FIELDS PSF WHERE SUBSTR(PSF.REFERENCE_CODE,0,LENGTH(p_reference_code))=p_reference_code AND PSF.PARAMETER_TOKEN= p_token_start_table);

P_SET_FIELD_ID KNTA_PARAMETER_SET_FIELDS.PARAMETER_SET_FIELD_ID%TYPE;
P_SET_CONTEXT_ID KNTA_PARAMETER_SET_FIELDS.PARAMETER_SET_CONTEXT_ID%TYPE;

BEGIN

SELECT PSF.PARAMETER_SET_FIELD_ID,PARAMETER_SET_CONTEXT_ID
INTO P_SET_FIELD_ID,P_SET_CONTEXT_ID
FROM KNTA_PARAMETER_SET_FIELDS PSF
WHERE SUBSTR(PSF.REFERENCE_CODE,0,LENGTH(p_reference_code))=p_reference_code AND PSF.PARAMETER_TOKEN= p_token_end_table;

DELETE FROM KCRT_TABLE_ENTRIES TE WHERE TE.REQUEST_ID = p_request_id and PARAMETER_SET_FIELD_ID=P_SET_FIELD_ID and PARAMETER_SET_CONTEXT_ID=P_SET_CONTEXT_ID;

FOR CUR_DATOS IN OBTENER_DATOS_TABLA LOOP

INSERT INTO KCRT_TABLE_ENTRIES(
SELECT KCRT_TABLE_ENTRIES_S.NEXTVAL,
CUR_DATOS.CREATED_BY,
SYSDATE,
CUR_DATOS.LAST_UPDATED_BY,
SYSDATE,
CUR_DATOS.REQUEST_ID,
P_SET_FIELD_ID,
CUR_DATOS.SEQ,
P_SET_CONTEXT_ID,
CUR_DATOS.PARAMETER1,CUR_DATOS.VISIBLE_PARAMETER1,
CUR_DATOS.PARAMETER2,CUR_DATOS.VISIBLE_PARAMETER2,
CUR_DATOS.PARAMETER3,CUR_DATOS.VISIBLE_PARAMETER3,
CUR_DATOS.PARAMETER4,CUR_DATOS.VISIBLE_PARAMETER4,
CUR_DATOS.PARAMETER5,CUR_DATOS.VISIBLE_PARAMETER5,
CUR_DATOS.PARAMETER6,CUR_DATOS.VISIBLE_PARAMETER6,
CUR_DATOS.PARAMETER7,CUR_DATOS.VISIBLE_PARAMETER7,
CUR_DATOS.PARAMETER8,CUR_DATOS.VISIBLE_PARAMETER8,
CUR_DATOS.PARAMETER9,CUR_DATOS.VISIBLE_PARAMETER9,
CUR_DATOS.PARAMETER10,CUR_DATOS.VISIBLE_PARAMETER10,
CUR_DATOS.PARAMETER11,CUR_DATOS.VISIBLE_PARAMETER11,
CUR_DATOS.PARAMETER12,CUR_DATOS.VISIBLE_PARAMETER12,
CUR_DATOS.PARAMETER13,CUR_DATOS.VISIBLE_PARAMETER13,
CUR_DATOS.PARAMETER14,CUR_DATOS.VISIBLE_PARAMETER14,
CUR_DATOS.PARAMETER15,CUR_DATOS.VISIBLE_PARAMETER15,
CUR_DATOS.PARAMETER16,CUR_DATOS.VISIBLE_PARAMETER16,
CUR_DATOS.PARAMETER17,CUR_DATOS.VISIBLE_PARAMETER17,
CUR_DATOS.PARAMETER18,CUR_DATOS.VISIBLE_PARAMETER18,
CUR_DATOS.PARAMETER19,CUR_DATOS.VISIBLE_PARAMETER19,
CUR_DATOS.PARAMETER20,CUR_DATOS.VISIBLE_PARAMETER20,
CUR_DATOS.PARAMETER21,CUR_DATOS.VISIBLE_PARAMETER21,
CUR_DATOS.PARAMETER22,CUR_DATOS.VISIBLE_PARAMETER22,
CUR_DATOS.PARAMETER23,CUR_DATOS.VISIBLE_PARAMETER23,
CUR_DATOS.PARAMETER24,CUR_DATOS.VISIBLE_PARAMETER24,
CUR_DATOS.PARAMETER25,CUR_DATOS.VISIBLE_PARAMETER25,
CUR_DATOS.PARAMETER26,CUR_DATOS.VISIBLE_PARAMETER26,
CUR_DATOS.PARAMETER27,CUR_DATOS.VISIBLE_PARAMETER27,
CUR_DATOS.PARAMETER28,CUR_DATOS.VISIBLE_PARAMETER28,
CUR_DATOS.PARAMETER29,CUR_DATOS.VISIBLE_PARAMETER29,
CUR_DATOS.PARAMETER30,CUR_DATOS.VISIBLE_PARAMETER30,
CUR_DATOS.PARAMETER31,CUR_DATOS.VISIBLE_PARAMETER31,
CUR_DATOS.PARAMETER32,CUR_DATOS.VISIBLE_PARAMETER32,
CUR_DATOS.PARAMETER33,CUR_DATOS.VISIBLE_PARAMETER33,
CUR_DATOS.PARAMETER34,CUR_DATOS.VISIBLE_PARAMETER34,
CUR_DATOS.PARAMETER35,CUR_DATOS.VISIBLE_PARAMETER35,
CUR_DATOS.PARAMETER36,CUR_DATOS.VISIBLE_PARAMETER36,
CUR_DATOS.PARAMETER37,CUR_DATOS.VISIBLE_PARAMETER37,
CUR_DATOS.PARAMETER38,CUR_DATOS.VISIBLE_PARAMETER38,
CUR_DATOS.PARAMETER39,CUR_DATOS.VISIBLE_PARAMETER39,
CUR_DATOS.PARAMETER40,CUR_DATOS.VISIBLE_PARAMETER40,
CUR_DATOS.PARAMETER41,CUR_DATOS.VISIBLE_PARAMETER41,
CUR_DATOS.PARAMETER42,CUR_DATOS.VISIBLE_PARAMETER42,
CUR_DATOS.PARAMETER43,CUR_DATOS.VISIBLE_PARAMETER43,
CUR_DATOS.PARAMETER44,CUR_DATOS.VISIBLE_PARAMETER44,
CUR_DATOS.PA
RAMETER45,CUR_DATOS.VISIBLE_PARAMETER45,
CUR_DATOS.PARAMETER46,CUR_DATOS.VISIBLE_PARAMETER46,
CUR_DATOS.PARAMETER47,CUR_DATOS.VISIBLE_PARAMETER47,
CUR_DATOS.PARAMETER48,CUR_DATOS.VISIBLE_PARAMETER48,
CUR_DATOS.PARAMETER49,CUR_DATOS.VISIBLE_PARAMETER49,
CUR_DATOS.PARAMETER50,CUR_DATOS.VISIBLE_PARAMETER50
FROM DUAL);

END LOOP;


COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;

END CopiarTablaOD;

 

3 REPLIES

Re: Copy Table Data between tables

I think this query could work

 

SELECT MAX(DISTINCT(PSF2.PARAMETER_SET_CONTEXT_ID))
INTO P_SET_CONTEXT_ID_DES
FROM KNTA_PARAMETER_SET_FIELDS PSF,
KNTA_PARAMETER_SET_CONTEXTS PST,
KCRT_REQUEST_TYPES RT,
KNTA_VALIDATIONS V
,KNTA_PARAMETER_SET_FIELDS PSF2
WHERE 1=1
AND SUBSTR(PSF.REFERENCE_CODE,0,LENGTH(p_reference_code))=p_reference_code
AND PSF.PARAMETER_TOKEN =p_token_tabla_destino
AND PSF.PARAMETER_SET_CONTEXT_ID = PST.PARAMETER_SET_CONTEXT_ID
AND RT.REQUEST_TYPE_ID = PST.CONTEXT_VALUE
AND V.VALIDATION_ID = PSF.VALIDATION_ID
AND PSF2.REFERENCE_CODE like (V.REFERENCE_CODE || '%');

Utkarsh_Mishra
Honored Contributor
Solution

Re: Copy Table Data between tables

Another way using request_type_id

 

select  KPSF.PARAMETER_SET_FIELD_ID, KPSC.PARAMETER_SET_CONTEXT_ID   
from knta_parameter_set_fields kpsf
join   knta_parameter_set_contexts kpsc on 
        (KPSC.CONTEXT_VALUE = to_char(KPSF.VALIDATION_ID)
        and kpsc.entity_id = 13)
join   knta_parameter_set_contexts kpsc1 on
        (kpsc1.context_value = to_char(<REQUEST_TYPE_ID>) --REQUEST_TYPE_ID
         and kpsc1.entity_id = 19
         and kpsc1.parameter_set_context_id = kpsf.parameter_set_context_id)                            
where KPSF.PARAMETER_TOKEN =   '<TC_TOKEN>'   -- TABLE COMPONENT TOKEN

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)

Re: Copy Table Data between tables

Thanks Utkarsh, you are the best!

//Add this to "OnDomLoad" event