[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SOURCE_DENORM');
/* This piece of code is for the objects that had an update somewhere in their hierarachy chain. */
DELETE bim_i_source_denorm
WHERE source_code_id IN
(SELECT source_code_id
FROM bim_i_source_codes a
WHERE obj_last_update_date > l_temp_start_date
);
/*DELETE bim_i_source_denorm
WHERE source_code_id IN
(
SELECT source_code_id
FROM bim_i_source_denorm
WHERE parent_source_code_id IN
(
SELECT b.source_code_id
FROM ams_campaigns_all_b a,
bim_i_source_codes b
WHERE a.rollup_type in ('RCAM')
and b.source_code_id = (-1)*b.object_id
AND obj_last_update_date > l_temp_start_date
)
);*/
DELETE bim_i_source_denorm
WHERE source_code_id IN
(
SELECT source_code_id
FROM bim_i_source_denorm
WHERE parent_source_code_id IN
(
SELECT b.source_code_id
FROM bim_i_source_codes b
WHERE b.source_code_id = (-1)*b.object_id
AND obj_last_update_date > l_temp_start_date
)
);
UPDATE bim_i_source_denorm SET leaf_node_flag = 'N'
WHERE source_code_id
IN
(
SELECT parent_source_code_id
FROM bim_i_source_codes a
WHERE rollup_type in ('CSCH', 'EVE0')
AND NOT EXISTS (
SELECT 1
FROM bim_i_source_denorm b
WHERE b.source_code_id = a.source_code_id
)
AND object_level = 1
);
INSERT INTO bim_i_source_denorm
(source_code_id
,parent_source_code_id
,immediate_parent_flag
,immediate_parent_id
,prior_id
,object_level
,rollup_type
,parent_source_code_type
,top_node_flag
,leaf_node_flag
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
)
SELECT
x.source_code_id
,x.parent_source_code_id
,x.immediate_parent_flag
,x.immediate_parent_id
,s.parent_source_code_id
,x.object_level
,x.object_type
,x.parent_source_code_type
,decode(s.parent_source_code_id, NULL, 'Y', 'N')
,(CASE
WHEN (x.leaf_node_flag = 'Y' AND x.object_level = 1)
THEN 'Y'
ELSE 'N'
END)
--,decode(x.object_level,1,'Y','N')
,sysdate
,sysdate
,-1
,-1
,-1
FROM
(
SELECT
source_code_id source_code_id
,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(source_code_id,'/'),2,
INSTR(SYS_CONNECT_BY_PATH(source_code_id,'/'),'/',2) -2),source_code_id)) AS parent_source_code_id
,decode(parent_source_code_id,NULL,'Y',decode(level,2,'Y','N')) immediate_parent_flag
,parent_source_code_id immediate_parent_id
,LEVEL object_level
,rollup_type object_type
,NVL(PRIOR(object_type),object_type) parent_source_code_type
,decode(parent_source_code_id, NULL, 'Y', 'N') top_node_flag
,(CASE
WHEN rollup_type in ('CSCH','EVEO','EONE') THEN 'Y'
WHEN source_code_id < 0 THEN 'N'
WHEN (select 'Y' from bim_i_source_codes b
where a.object_id = b.object_id
and a.object_type = b.object_type
and b.object_type in ('CAMP','EVEH')
and b.child_object_id > 0
and rownum = 1) is NULL THEN 'Y'
ELSE 'N'
END
) leaf_node_flag
FROM bim_i_source_codes a
WHERE
NOT EXISTS
(SELECT 1
FROM BIM_I_SOURCE_DENORM b
WHERE b.source_code_id = a.source_code_id
-- AND nvl(b.parent_object_id,1) = nvl(a.parent_campaign_id,1)
)
CONNECT BY PRIOR source_code_id = parent_source_code_id ) x,
BIM_I_SOURCE_CODES s
WHERE s.source_code_id = x.parent_source_code_id;
INSERT INTO bim_i_source_denorm
(source_code_id
,parent_source_code_id
,immediate_parent_flag
,immediate_parent_id
,prior_id
,object_level
,rollup_type
,parent_source_code_type
,top_node_flag
,leaf_node_flag
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
)
SELECT
x.source_code_id source_code_id
,x.parent_source_code_id parent_source_code_id
,x.immediate_parent_flag immediate_parent_flag
,x.immediate_parent_id immediate_parent_id
,s.parent_source_code_id prior_id
,x.object_level object_level
,x.object_type object_type
,x.parent_source_code_type
,decode(s.parent_source_code_id, NULL, 'Y', 'N') top_node_flag
,(CASE
WHEN (x.leaf_node_flag = 'Y' AND x.object_level = 1)
THEN 'Y'
ELSE 'N'
END) leaf_node_flag
--,decode(x.object_level,1,'Y','N') leaf_node_flag
,sysdate
,sysdate
,-1
,-1
,-1
FROM
(
SELECT
source_code_id source_code_id
,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(source_code_id,'/'),2,
INSTR(SYS_CONNECT_BY_PATH(source_code_id,'/'),'/',2) -2),source_code_id)) AS parent_source_code_id
,decode(parent_source_code_id,NULL,'Y',decode(level,2,'Y','N')) immediate_parent_flag
,parent_source_code_id immediate_parent_id
,LEVEL object_level
,rollup_type object_type
,NVL(PRIOR(object_type),object_type) parent_source_code_type
,decode(parent_source_code_id, NULL, 'Y', 'N') top_node_flag
,(CASE
WHEN rollup_type in ('CSCH','EVEO','EONE') THEN 'Y'
WHEN source_code_id < 0 THEN 'N'
WHEN (select 'Y' from bim_i_source_codes b
where a.object_id = b.object_id
and a.object_type = b.object_type
and b.object_type in ('CAMP','EVEH')
and b.child_object_id > 0
and rownum = 1) is NULL THEN 'Y'
ELSE 'N'
END
) leaf_node_flag
FROM bim_i_source_codes a
CONNECT BY PRIOR source_code_id = parent_source_code_id ) x,
BIM_I_SOURCE_CODES s
WHERE s.source_code_id = x.parent_source_code_id
UNION ALL
SELECT
-1 source_code_id
,-1 parent_source_code_id
,'Y' immediate_parent_flag
,null immediate_parent_id
,null prior_id
,1 object_level
,null object_type
,null parent_source_code_type
,'Y' top_node_flag
,'Y' leaf_node_flag
,sysdate
,sysdate
,-1
,-1
,-1
FROM dual ;
INSERT INTO bim_i_admin_group
(
Resource_Id
)
SELECT resource_id
FROM jtf_rs_group_members
WHERE group_id = fnd_profile.value('AMS_ADMIN_GROUP')
AND delete_flag ='N';
INSERT INTO bim_i_top_objects
(
resource_id,
source_code_id ,
object_id,
object_type,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login)
SELECT c.resource_id,
b.parent_source_code_id,
null,
null,
sysdate,
sysdate,
-1,
-1,
-1
FROM bim_i_source_denorm b,
(
SELECT
a.resource_id,code1.source_code_id,
max(b.object_level) object_level ,
a.object_type object_type
FROM ams_act_access_denorm a,
bim_i_source_denorm b,
bim_i_source_codes code1,
ams_act_access_denorm c,
bim_i_source_codes code2
WHERE a.object_id = code1.object_id
AND a.object_type = code1.object_type
AND b.source_code_id=code1.source_code_id
AND code1.object_type in ('RCAM', 'CAMP', 'EVEH', 'EONE')
AND code1.child_object_id=0
AND a.edit_metrics_yn = 'Y'
AND NOT EXISTS
(SELECT resource_id FROM bim_i_admin_group WHERE resource_id = a.resource_id)
AND c.resource_id = a.resource_id
AND c.object_id = code2.object_id
AND c.object_type = code2.object_type
AND code2.source_code_id=b.parent_source_code_id
AND c.edit_metrics_yn = 'Y'
GROUP BY a.resource_id, code1.source_code_id, a.object_type) c
WHERE c.object_level = b.object_level
AND b.source_code_id = c.source_code_id
GROUP BY c.resource_id,b.parent_source_code_id;