Absent Member.. AlfredoMonasi Absent Member..
Absent Member..
553 views

Copy Table Data between tables

Jump to solution

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;

 

0 Likes
1 Solution

Accepted Solutions
Established Member.. Utkarsh_Mishra
Established Member..

Re: Copy Table Data between tables

Jump to solution

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)
3 Replies
Absent Member.. AlfredoMonasi Absent Member..
Absent Member..

Re: Copy Table Data between tables

Jump to solution

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 || '%');

0 Likes
Established Member.. Utkarsh_Mishra
Established Member..

Re: Copy Table Data between tables

Jump to solution

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)
Absent Member.. AlfredoMonasi Absent Member..
Absent Member..

Re: Copy Table Data between tables

Jump to solution

Thanks Utkarsh, you are the best!

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.