CREATE COLUMN TABLE "ZTABLES"."ZOBJ_DUP_DEL_LIST" ("LEVEL" NVARCHAR(256),
"OBJECT_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECTS" NVARCHAR(256) NOT NULL ,
"PACKAGE_ID" NVARCHAR(256) NOT NULL ,
"OBJECT_SUFFIX" NVARCHAR(255) NOT NULL ,
"BASE_SCHEMA_NAME" NVARCHAR(256),
"BASE_OBJECT_NAME" NVARCHAR(256),
"BASE_OBJECT_TYPE" VARCHAR(32),
"DEPENDENT_SCHEMA_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_TYPE" VARCHAR(32) NOT NULL ) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "ZTABLES"."ZOBJ_DEP_LIST" ("LEVEL" INTEGER CS_INT,
"OBJECT_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECTS" NVARCHAR(256) NOT NULL ,
"PACKAGE_ID" NVARCHAR(256) NOT NULL ,
"OBJECT_SUFFIX" NVARCHAR(255) NOT NULL ,
"BASE_SCHEMA_NAME" NVARCHAR(256),
"BASE_OBJECT_NAME" NVARCHAR(256),
"BASE_OBJECT_TYPE" VARCHAR(32),
"DEPENDENT_SCHEMA_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_TYPE" VARCHAR(32) NOT NULL ) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "ZTABLES"."ZOBJ_SP_XSJS_LIST" ("FROM_PACKAGE_ID" NVARCHAR(256) NOT NULL ,
"FROM_OBJECT_NAME" NVARCHAR(256),
"FROM_OBJECT_SUFFIX" NVARCHAR(256),
"TO_PACKAGE_ID" NVARCHAR(256) NOT NULL ,
"TO_OBJECT_NAME" NVARCHAR(256)) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "ZTABLES"."ZOBJ_WUL_SP_LIST" ("TYPE" VARCHAR(32),
"BASE_SCHEMA_NAME" NVARCHAR(256),
"BASE_OBJECT_NAME" NVARCHAR(256),
"BASE_OBJECT_TYPE" VARCHAR(32),
"DEPENDENT_SCHEMA_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_TYPE" VARCHAR(32) NOT NULL ) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "ZTABLES"."ZINPUT_MODELS" ("ZINPUT_MODELS" VARCHAR(250)) UNLOAD PRIORITY 5 AUTO MERGE
PROCEDURE "ZTABLES"."ZTABLES::ZDEPENDENCIES_INPUT_MODELS" (
--IN count VARCHAR(2),
IN Module VARCHAR(4)
-- Use the below line of code in case single calculation view is to be passed as input
--,IN MODEL TABLE (ZINPUT_MODELS VARCHAR(250))
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS BEGIN
DECLARE count VARCHAR(2):= 9;
DECLARE number VARCHAR(2) := 0;
DECLARE SP_ROW VARCHAR(2) := 0;
DECLARE SP_INPUT NVARCHAR(256);
create local temporary table #mytemp(
RN NVARCHAR(256),
LEVEL INTEGER,
OBJECT_NAME NVARCHAR(256) NOT NULL,
DEPENDENT_OBJECTS NVARCHAR(256) NOT NULL,
PACKAGE_ID NVARCHAR(256) NOT NULL,
OBJECT_SUFFIX NVARCHAR(255) NOT NULL,
BASE_SCHEMA_NAME NVARCHAR(256),
BASE_OBJECT_NAME NVARCHAR(256),
BASE_OBJECT_TYPE VARCHAR(32),
DEPENDENT_SCHEMA_NAME NVARCHAR(256) NOT NULL,
DEPENDENT_OBJECT_NAME NVARCHAR(256) NOT NULL,
DEPENDENT_OBJECT_TYPE VARCHAR(32) NOT NULL
);
create local temporary table #mytemp_SP1(
FROM_OBJECT_NAME NVARCHAR(256)
);
create local temporary table #mytemp_SP2(
ROW_NUMBER INTEGER,
FROM_OBJECT_NAME NVARCHAR(256)
);
create local temporary table #mytemp_xsjs(
PACKAGE_ID NVARCHAR(256),
XSJS_JOB NVARCHAR(256),
OBJECT_SUFFIX NVARCHAR(256),
TO_PACKAGE_ID NVARCHAR(256),
STORED_PROCEDURE NVARCHAR(256)
);
--Deletion of tables before every run
delete from "ZTABLES"."ZOBJ_DEP_LIST";
delete from "ZTABLES"."ZOBJ_DUP_DEL_LIST";
delete from "ZTABLES"."ZOBJ_WUL_SP_LIST";
delete from "ZTABLES"."ZOBJ_SP_XSJS_LIST";
--Deletion of tables before every run
--Fetching the level1 data from standard tables for entered input CV's
INSERT INTO "ZTABLES"."ZOBJ_DEP_LIST"
(
SELECT
od.DEPENDENCY_TYPE AS LEVEL, ao.OBJECT_NAME
, CASE WHEN od.BASE_SCHEMA_NAME = '_SYS_BIC' THEN SUBSTR_AFTER (od.BASE_OBJECT_NAME,'/')
WHEN od.BASE_SCHEMA_NAME = 'ZTABLES' THEN SUBSTR_AFTER (od.BASE_OBJECT_NAME,'::')
ELSE od.BASE_OBJECT_NAME
END AS DEPENDENT_OBJECTS,
ao.PACKAGE_ID,
ao.OBJECT_SUFFIX, od.BASE_SCHEMA_NAME, od.BASE_OBJECT_NAME, od.BASE_OBJECT_TYPE,
od.DEPENDENT_SCHEMA_NAME, od.DEPENDENT_OBJECT_NAME, od.DEPENDENT_OBJECT_TYPE
FROM _SYS_REPO.ACTIVE_OBJECT ao
INNER JOIN OBJECT_DEPENDENCIES od
ON '_SYS_BIC' = od.DEPENDENT_SCHEMA_NAME
AND ao.PACKAGE_ID||'/'||ao.OBJECT_NAME = od.DEPENDENT_OBJECT_NAME
AND 'VIEW' = od.DEPENDENT_OBJECT_TYPE
AND '1' = od.DEPENDENCY_TYPE
WHERE ao.OBJECT_NAME
--WHERE ao.OBJECT_NAME = :MODEL
-- Comment below line and use the above line in case single calculation view is to be passed as input
in (SELECT * FROM "ZTABLES"."ZINPUT_MODELS")
ORDER BY
"LEVEL",
"OBJECT_NAME",
"DEPENDENT_OBJECTS",
"PACKAGE_ID",
"OBJECT_SUFFIX",
"BASE_SCHEMA_NAME",
"BASE_OBJECT_NAME",
"BASE_OBJECT_TYPE",
"DEPENDENT_SCHEMA_NAME",
"DEPENDENT_OBJECT_NAME",
"DEPENDENT_OBJECT_TYPE"
ASC
);
--Fetching the level1 data from standard tables for entered input CV's
count := :count - 1;
WHILE :count > 0 DO
count := :count - 1;
number := :number + 1;
--Taking the level1 dataset as the input for level2 and henceforth
INSERT INTO "ZTABLES"."ZOBJ_DEP_LIST"
(
SELECT :number +1 AS LEVEL, ao.OBJECT_NAME
, CASE WHEN od.BASE_SCHEMA_NAME = '_SYS_BIC' THEN SUBSTR_AFTER (od.BASE_OBJECT_NAME,'/')
ELSE od.BASE_OBJECT_NAME
END AS DEPENDENT_OBJECTS,
ao.PACKAGE_ID,
ao.OBJECT_SUFFIX, od.BASE_SCHEMA_NAME, od.BASE_OBJECT_NAME, od.BASE_OBJECT_TYPE,
od.DEPENDENT_SCHEMA_NAME, od.DEPENDENT_OBJECT_NAME, od.DEPENDENT_OBJECT_TYPE
FROM _SYS_REPO.ACTIVE_OBJECT ao
INNER JOIN OBJECT_DEPENDENCIES od
ON '_SYS_BIC' = od.DEPENDENT_SCHEMA_NAME
AND ao.PACKAGE_ID||'/'||ao.OBJECT_NAME = od.DEPENDENT_OBJECT_NAME
AND 'VIEW' = od.DEPENDENT_OBJECT_TYPE
AND '1' = od.DEPENDENCY_TYPE
WHERE ao.OBJECT_NAME in (SELECT DEPENDENT_OBJECTS FROM "ZTABLES"."ZOBJ_DEP_LIST" WHERE LEVEL = :number)
AND ao.OBJECT_SUFFIX = 'calculationview'
ORDER BY
"LEVEL",
"OBJECT_NAME",
"DEPENDENT_OBJECTS",
"PACKAGE_ID",
"OBJECT_SUFFIX",
"BASE_SCHEMA_NAME",
"BASE_OBJECT_NAME",
"BASE_OBJECT_TYPE",
"DEPENDENT_SCHEMA_NAME",
"DEPENDENT_OBJECT_NAME",
"DEPENDENT_OBJECT_TYPE"
ASC
);
--Taking the level1 dataset as the input for level2 and henceforth
END WHILE;
--Logic to delete duplicate records
INSERT INTO #mytemp
(
SELECT ROW_NUMBER() OVER (PARTITION BY
"OBJECT_NAME",
"DEPENDENT_OBJECTS",
"PACKAGE_ID"
ORDER BY
"LEVEL" DESC
) as RN, *
FROM "ZTABLES"."ZOBJ_DEP_LIST"
);
delete from #mytemp where RN <> 1;
INSERT INTO "ZTABLES"."ZOBJ_DUP_DEL_LIST"
(
SELECT
"LEVEL",
"OBJECT_NAME",
"DEPENDENT_OBJECTS",
"PACKAGE_ID",
"OBJECT_SUFFIX",
"BASE_SCHEMA_NAME",
"BASE_OBJECT_NAME",
"BASE_OBJECT_TYPE",
"DEPENDENT_SCHEMA_NAME",
"DEPENDENT_OBJECT_NAME",
"DEPENDENT_OBJECT_TYPE"
FROM #mytemp order by "LEVEL", "OBJECT_NAME" ASC
);
drop table #mytemp;
--Logic to delete duplicate records
--To fetch Where-Used List for the entered input CV's
INSERT INTO "ZTABLES"."ZOBJ_WUL_SP_LIST"
(
SELECT
'WUL_INPUT_CV',
BASE_SCHEMA_NAME,
BASE_OBJECT_NAME,
BASE_OBJECT_TYPE,
DEPENDENT_SCHEMA_NAME,
DEPENDENT_OBJECT_NAME,
DEPENDENT_OBJECT_TYPE
FROM "SYS"."OBJECT_DEPENDENCIES"
WHERE
BASE_OBJECT_NAME IN(
SELECT distinct OD.PACKAGE_ID||'/'||OD.OBJECT_NAME AS MODEL
FROM "ZTABLES"."ZOBJ_DEP_LIST" OD
INNER JOIN "ZTABLES"."ZINPUT_MODELS" IM
ON OD.OBJECT_NAME = IM.ZINPUT_MODELS
)
AND DEPENDENCY_TYPE = '1'
AND DEPENDENT_OBJECT_NAME in (
SELECT BASE_OBJECT_NAME FROM "SYS"."OBJECT_DEPENDENCIES"
WHERE DEPENDENCY_TYPE = '1'
)
);
--To fetch Where-Used List for the entered input CV's
--To fetch Stored Procedure list for the entered input CV's
INSERT INTO "ZTABLES"."ZOBJ_WUL_SP_LIST"
(
SELECT
'STORED PROCEDURE',
FROM_PACKAGE_ID,
FROM_OBJECT_NAME,
FROM_OBJECT_SUFFIX,
TO_PACKAGE_ID,
TO_OBJECT_NAME,
TO_OBJECT_SUFFIX
FROM _SYS_REPO.active_objectcrossref
WHERE FROM_OBJECT_NAME in (
SELECT FROM_OBJECT_NAME FROM _SYS_REPO.active_objectcrossref
WHERE TO_OBJECT_NAME in (
SELECT distinct DEPENDENT_OBJECTS FROM "ZTABLES"."ZOBJ_DEP_LIST"
WHERE BASE_SCHEMA_NAME = 'ZTABLES'
AND FROM_OBJECT_SUFFIX = 'hdbprocedure'
))
);
--To fetch Stored Procedure list for the entered input CV's
--To fetch Stored Procedure, xsjob list along with the CV and ZTABLE for the entered input module
INSERT INTO "ZTABLES"."ZOBJ_SP_XSJS_LIST"
(
SELECT
FROM_PACKAGE_ID,
FROM_OBJECT_NAME,
FROM_OBJECT_SUFFIX,
TO_PACKAGE_ID,
TO_OBJECT_NAME
FROM _SYS_REPO.active_objectcrossref
WHERE
FROM_OBJECT_SUFFIX like 'hd%'
AND FROM_OBJECT_NAME like :Module
ORDER BY FROM_OBJECT_NAME ASC
);
INSERT INTO "ZTABLES"."ZOBJ_SP_XSJS_LIST"
(
SELECT
FROM_PACKAGE_ID,
FROM_OBJECT_NAME,
FROM_OBJECT_SUFFIX,
TO_PACKAGE_ID,
TO_OBJECT_NAME
FROM _SYS_REPO.active_objectcrossref
WHERE
FROM_OBJECT_SUFFIX like 'xs%'
AND FROM_OBJECT_NAME like :Module
ORDER BY FROM_OBJECT_NAME ASC
);
--To fetch Stored Procedure, xsjob list along with the CV and ZTABLE for the entered input module
--To fetch xsjs list for the entered input module
INSERT INTO #mytemp_SP1
(
SELECT DISTINCT
FROM_OBJECT_NAME
FROM "ZTABLES"."ZOBJ_SP_XSJS_LIST"
);
INSERT INTO #mytemp_SP2
(
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY FROM_OBJECT_NAME) AS ROW_NUMBER,
FROM_OBJECT_NAME
FROM #mytemp_SP1
);
SELECT count(*) into SP_ROW FROM #mytemp_SP2;
WHILE :SP_ROW > 0 DO
SELECT FROM_OBJECT_NAME into SP_INPUT FROM #mytemp_SP2 WHERE ROW_NUMBER = :SP_ROW;
SP_ROW := :SP_ROW - 1;
INSERT INTO #mytemp_xsjs
(
SELECT PACKAGE_ID, OBJECT_NAME, OBJECT_SUFFIX, '', :SP_INPUT FROM _SYS_REPO.ACTIVE_OBJECT
WHERE CONTAINS(CDATA, :SP_INPUT)
ORDER BY OBJECT_NAME
);
END WHILE;
DELETE FROM #mytemp_xsjs WHERE OBJECT_SUFFIX <> 'xsjs';
INSERT INTO "ZTABLES"."ZOBJ_SP_XSJS_LIST" ( SELECT * FROM #mytemp_xsjs);
--To fetch xsjs list for the entered input module
drop table #mytemp_SP1;
drop table #mytemp_SP2;
drop table #mytemp_xsjs;
END;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
7 | |
5 | |
4 | |
4 | |
4 | |
4 | |
4 | |
3 | |
3 |