Stored Procedure to check the Change record movement Status across Landscape in CTS+

This blog will show how to use the system table “_SYS_REPO”.“CHANGES” to check the Transport movement successful or not in CTS+.

Many a times we face difficulty in checking the change record movement across landscape, because:

1. 1. Transport Request, which is created in SOLMAN will show Error Code 8 but we need to ignore this after checking the Changes in SQL or Field, individually in the system.
2. 2. We have to make frequent decision to move the back-out TR, but face many difficulty in finding dependency, as the CTS+ message would not be clearly stating the dependency.
3. 3. Modeller might not be around to quickly guess, if we have some table creation error/Table updating error.

So I have created this Stored Procedure, which will quickly check the change record if moved or Not moved.

Code Snippet for Stored Procedure:

 /********* Begin Procedure Script ************/
BEGIN
-- Declare variables
DECLARE VAR1 NVARCHAR(10);
select count(*) INTO var1
from "_SYS_REPO"."CHANGES"
where  CHANGE_NUMBER = :IP_CHANGE_ID
or TO_CHAR(SRC_SYSTEM||'//'||CHANGE_NUMBER) = :IP_CHANGE_ID;
if var1 > 0
then
OUT_TABLE =  select 'Yes moved' as RETURN_VAL from dummy;
else
OUT_TABLE =  select 'Not Moved' as RETURN_VAL from dummy;
end if;
END;
 /********* End Procedure Script ************/
call "_SYS_BIC"."users.Chandan/SP_TEST_CHANGE_DONE"('29012',NULL);

Hope this logic you can also use to quicken the validation.

I welcome the change or ammendement of this document.

Cheers!

Chandan