[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure Propogate_DeletedFlag is
TYPE tPublTable IS TABLE OF CZ_MODEL_PUBLICATIONS.PUBLICATION_ID%TYPE INDEX BY BINARY_INTEGER;
deletedPublsTbl tPublTable;
deletedPubl CZ_MODEL_REF_EXPLS.COMPONENT_ID%TYPE;
deletedPKey CZ_DEVL_PROJECTS.PRODUCT_KEY%TYPE;
/* All deleted publications*/
--Bug12653412: SKM - Adding an additional condition in order to run
--publication and purge at the same time. During publication we first create the
--records in cz_devl_projects and at much latter stage to cz_model_ref_expls.
--So, excluding such records not to be purge candidates.
--Bug#12653412: Additional condition added for the models with on-going
--publications are marked as published date not null. DO NOT select such models.
CURSOR cDeletedPublication IS
SELECT devl_project_id, NVL(product_key,name)
FROM cz_devl_projects
WHERE deleted_flag = '0'
AND published IS NULL
AND EXISTS (SELECT null FROM cz_model_ref_expls
WHERE model_id=cz_devl_projects.devl_project_id
AND deleted_flag='0')
AND devl_project_id NOT IN (SELECT object_id FROM cz_rp_entries
WHERE object_type = 'PRJ'
AND deleted_flag = '0');
requiredModelTbl.DELETE;
hashRequiredModels.DELETE;
/* Get all published models, along with the child models that need to exist (undeleted models) */
SELECT distinct component_id
BULK COLLECT
INTO requiredModelTbl
FROM cz_model_ref_expls
WHERE deleted_flag = '0'
AND model_id IN (SELECT model_id FROM cz_model_publications
WHERE source_target_flag = 'T'
AND deleted_flag = '0')
CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id
ORDER BY component_id;
/* Loop through all deleted models and check if it is not being referenced
by other publications. If so, then mark it for deletion */
BEGIN
open cDeletedPublication;
FETCH cDeletedPublication into deletedPubl,deletedPKey;
EXIT WHEN cDeletedPublication%NOTFOUND;
IF (NOT (hashRequiredModels.EXISTS(deletedPubl))) THEN
BEGIN
/* Logically delete model */
UPDATE cz_devl_projects
SET deleted_flag = '1'
WHERE devl_project_id = deletedPubl
AND published IS NULL; /* Extra condition for over precaution*/
/*SKM: Added Loggin for deleted models / Product Key*/
/* Length of the db_logs is 4000 Max and hence inserting little less than that*/
vModelKeyStr := vModelKeyStr||deletedPKey||' - '||deletedPubl||'<-->';
CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG',vModelKeyStr, 'Tracking Deleted Projects',0,1);
CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG',vModelKeyStr, 'Tracking Deleted Projects',0,1);
close cDeletedPublication;
FOR i IN (SELECT object_id FROM CZ_RP_ENTRIES
WHERE enclosing_folder=p_folder_id AND object_type='FLD')
LOOP
l_folders_exist := TRUE;
PROCEDURE update_Objects_In_Folder(p_folder_id IN NUMBER,x_enclosing_folder OUT NOCOPY NUMBER) IS
l_object_id_tbl t_arr;
SELECT object_id, object_type
BULK COLLECT INTO l_object_id_tbl, l_object_type_tbl
FROM CZ_RP_ENTRIES
WHERE enclosing_folder=p_folder_id;
DELETE FROM CZ_RP_ENTRIES
WHERE object_id=l_object_id_tbl(i) AND object_type=l_object_type_tbl(i);
DELETE FROM CZ_RP_ENTRIES
WHERE object_id=p_folder_id AND object_type='FLD'
RETURNING enclosing_folder INTO x_enclosing_folder;
END update_Objects_In_Folder;
update_Objects_In_Folder(p_folder_id, l_enclosing_folder);
FOR i IN (SELECT object_id, enclosing_folder FROM CZ_RP_ENTRIES
WHERE object_id=l_enclosing_folder AND object_type='FLD')
LOOP
-- we should stop on this folder
IF i.object_id=p_subroot_folder_id THEN
update_Objects_In_Folder(p_folder_id, l_enclosing_folder);
FOR i IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_type='FLD' AND deleted_flag='1')
LOOP
--
-- array l_end_folder_ids_tbl will contain all subfolders
-- of folder i.object_id which have no subfolders
--
l_end_folder_ids_tbl.DELETE;
DELETE FROM CZ_RP_ENTRIES
WHERE enclosing_folder=i.object_id;
DELETE FROM CZ_RP_ENTRIES
WHERE object_id=i.object_id AND object_type='FLD';
Propogate_DeletedFlag;
delete from cz_pb_client_apps a
where not exists ( select null from cz_model_publications b
where b.publication_id = a.publication_id );
delete from cz_pb_languages a
where not exists ( select null from cz_model_publications b
where b.publication_id = a.publication_id );
delete from cz_publication_usages a
where not exists ( select null from cz_model_publications b
where b.publication_id = a.publication_id );
for i in(select server_id,model_id,
max(export_id) as max_export_id
from cz_pb_model_exports
where status='OK'
group by server_id,model_id)
loop
delete from cz_pb_model_exports
where export_id<>i.max_export_id and model_id=i.model_id and
server_id=i.server_id;