[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*) cnt,
company_id,
cost_center_id
from FII_COM_CC_MAPPINGS_GT
where company_cost_center_org_id <> -1
group by company_id, cost_center_id
having count(*) > 1;
select com.flex_value company,
cc.flex_value cost_center,
org.name organization,
org.organization_id org_id
from FII_COM_CC_MAPPINGS_GT gt,
hr_all_organization_units org,
fnd_flex_values com,
fnd_flex_values cc
where gt.company_id = p_com_id
and gt.cost_center_id = p_cc_id
and gt.company_cost_center_org_id = org.organization_id
and gt.company_id = com.flex_value_id
and gt.cost_center_id = cc.flex_value_id;
select emp_id, count(*)
from FII_CC_MGR_HIER_GT
where mgr_level = G_AGGREGATION_LEVELS
group by emp_id
having count(*) > 1;
select mgr_id, emp_level
from FII_CC_MGR_HIER_GT
where mgr_level = G_AGGREGATION_LEVELS
and emp_id = p_emp_id;
g_phase := 'Insert into FII_COM_CC_MAPPINGS by INITIAL_LOAD';
INSERT /*+ append*/ INTO FII_COM_CC_MAPPINGS
(COMPANY_COST_CENTER_ORG_ID ,
COST_CENTER_ID ,
COMPANY_ID ,
MANAGER_ID ,
VALID_MGR_FLAG,
LOB_ID,
PARENT_MANAGER_ID,
PARENT_LOB_ID,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN)
SELECT DISTINCT
dim.COMPANY_COST_CENTER_ORG_ID,
dim.COST_CENTER_ID,
dim.COMPANY_ID,
nvl(ct.manager, -1),
decode(ct.manager, NULL, 'N', 'Y'),
G_UNASSIGNED_LOB_ID,
nvl(ct.manager, -1),
G_UNASSIGNED_LOB_ID,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id
FROM FII_COM_CC_MAPPINGS_GT dim,
fii_ccc_mgr_gt ct
WHERE company_cost_center_org_id <> -1
and dim.company_cost_center_org_id = ct.CCC_ORG_ID (+);
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_CC_MAPPINGS');
g_phase := 'Insert into FII_COM_CC_MAPPINGS1_GT by INCREMENTAL_LOAD';
INSERT /*+ append*/ INTO FII_COM_CC_MAPPINGS1_GT
( COMPANY_COST_CENTER_ORG_ID ,
COST_CENTER_ID ,
COMPANY_ID ,
MANAGER_ID ,
VALID_MGR_FLAG,
LOB_ID,
PARENT_MANAGER_ID,
PARENT_LOB_ID)
SELECT DISTINCT
dim.COMPANY_COST_CENTER_ORG_ID,
dim.COST_CENTER_ID,
dim.COMPANY_ID,
nvl(ct.manager, -1),
decode(ct.manager, NULL, 'N', 'Y'),
G_UNASSIGNED_LOB_ID,
nvl(ct.manager, -1),
G_UNASSIGNED_LOB_ID
FROM FII_COM_CC_MAPPINGS_GT dim,
fii_ccc_mgr_gt ct
WHERE dim.company_cost_center_org_id <> -1
and dim.company_cost_center_org_id = ct.CCC_ORG_ID (+);
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_CC_MAPPINGS1_GT');
g_phase := 'Update LOB by INITIAL_LOAD_LOB';
fii_util.write_log('Update LOB_ID for DBI50');
UPDATE FII_COM_CC_MAPPINGS dim
SET dim.LOB_ID = NVL(
(SELECT NVL(x.c, -1)
FROM
(SELECT lob.LINE_OF_BUSINESS a,
lob.COMPANY_COST_CENTER_ORG_ID b,
flex.FLEX_VALUE_ID c
FROM (SELECT findim.MASTER_VALUE_SET_ID FLEX_VALUE_SET_ID
FROM FII_FINANCIAL_DIMENSIONS findim
WHERE DIMENSION_SHORT_NAME = 'FII_LOB') vset,
fii_lob_assignments lob,
fnd_flex_values flex
WHERE flex.FLEX_VALUE_SET_ID = vset.FLEX_VALUE_SET_ID
AND flex.flex_value = lob.LINE_OF_BUSINESS) x
WHERE dim.COMPANY_COST_CENTER_ORG_ID <> -1
AND x.b = dim.COMPANY_COST_CENTER_ORG_ID), dim.LOB_ID)
WHERE dim.lob_id = G_UNASSIGNED_LOB_ID ;
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS');
fii_util.write_log('Update LOB_ID for DBI60 and above');
UPDATE FII_COM_CC_MAPPINGS dim
SET dim.LOB_ID = NVL(
(SELECT decode (lob.b, 'GL_BALANCING', dim.company_id, dim.cost_center_id)
FROM FII_COM_CC_MAPPINGS_GT dim1,
(select map.chart_of_accounts_id a,
fsav.segment_attribute_type b
from FND_SEGMENT_ATTRIBUTE_VALUES fsav,
fii_dim_mapping_rules map
where fsav.application_id = 101
and fsav.id_flex_code = 'GL#'
and map.dimension_short_name = 'FII_LOB'
and map.chart_of_accounts_id = fsav.id_flex_num
and map.application_column_name1 = fsav.application_column_name
and fsav.attribute_value = 'Y'
and fsav.segment_attribute_type in ('GL_BALANCING', 'FA_COST_CTR')) lob
WHERE lob.a = dim1.COA_ID
AND dim.COMPANY_COST_CENTER_ORG_ID = dim1.COMPANY_COST_CENTER_ORG_ID
AND decode (lob.b, 'GL_BALANCING', dim.company_id, dim.cost_center_id) IN
(select flob.child_lob_id
from fii_full_lob_hiers flob
where flob.parent_lob_id = flob.child_lob_id)
), dim.LOB_ID)
WHERE dim.lob_id = G_UNASSIGNED_LOB_ID ;
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS');
g_phase := 'Update parent_manager_id by INITIAL_LOAD_LOB';
Update FII_COM_CC_MAPPINGS dim
Set dim.parent_manager_id =
NVL((select mgr.mgr_id
from FII_CC_MGR_HIER_GT mgr
where mgr.mgr_level = G_AGGREGATION_LEVELS
and mgr.emp_id = dim.manager_id), dim.manager_id);
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS');
g_phase := 'Update parent_lob_id by INITIAL_LOAD_LOB';
Update FII_COM_CC_MAPPINGS dim
Set dim.parent_lob_id =
NVL((select v.parent_lob_id
from (select flob.parent_lob_id, flob.child_lob_id, lob.child_level
from fii_full_lob_hiers flob,
fii_lob_hierarchies lob
where lob.child_lob_id = flob.parent_lob_id
order by lob.child_level DESC) v
where v.child_lob_id = dim.lob_id
and rownum = 1), dim.lob_id);
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS');
g_phase := 'Update LOB by INCREMENTAL_LOAD_LOB_MERGE';
fii_util.write_log('Update LOB_ID for DBI50');
UPDATE FII_COM_CC_MAPPINGS1_GT dim
SET dim.LOB_ID = NVL(
(SELECT NVL(x.c ,-1 )
FROM
(select lob.LINE_OF_BUSINESS a ,
lob.COMPANY_COST_CENTER_ORG_ID b ,
flex.FLEX_VALUE_ID c
from (SELECT findim.MASTER_VALUE_SET_ID FLEX_VALUE_SET_ID
FROM FII_FINANCIAL_DIMENSIONS findim
WHERE DIMENSION_SHORT_NAME = 'FII_LOB') vset ,
fii_lob_assignments lob ,
fnd_flex_values flex
where flex.FLEX_VALUE_SET_ID = vset.FLEX_VALUE_SET_ID
and flex.flex_value = lob.LINE_OF_BUSINESS) x
WHERE dim.COMPANY_COST_CENTER_ORG_ID <> -1
AND x.b = dim.COMPANY_COST_CENTER_ORG_ID), dim.LOB_ID)
WHERE dim.lob_id = G_UNASSIGNED_LOB_ID ;
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS1_GT');
fii_util.write_log('Update LOB_ID for DBI60 and above');
UPDATE FII_COM_CC_MAPPINGS1_GT dim
SET dim.LOB_ID = NVL(
(select decode (lob.b, 'GL_BALANCING', dim.company_id, dim.cost_center_id)
from FII_COM_CC_MAPPINGS_GT dim1,
(select map.chart_of_accounts_id a,
fsav.segment_attribute_type b
from FND_SEGMENT_ATTRIBUTE_VALUES fsav,
fii_dim_mapping_rules map
where fsav.application_id = 101
and fsav.id_flex_code = 'GL#'
and map.dimension_short_name = 'FII_LOB'
and map.chart_of_accounts_id = fsav.id_flex_num
and map.application_column_name1 = fsav.application_column_name
and fsav.attribute_value = 'Y'
and fsav.segment_attribute_type in ('GL_BALANCING', 'FA_COST_CTR')) lob
where lob.a = dim1.COA_ID
and dim.COMPANY_COST_CENTER_ORG_ID = dim1.COMPANY_COST_CENTER_ORG_ID
and decode (lob.b, 'GL_BALANCING', dim.company_id, dim.cost_center_id) IN
(select flob.child_lob_id
from fii_full_lob_hiers flob
where flob.parent_lob_id = flob.child_lob_id)
), dim.LOB_ID)
WHERE dim.lob_id = G_UNASSIGNED_LOB_ID ;
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS1_GT');
g_phase := 'Update parent_manager_id by INCREMENTAL_LOAD_LOB_MERGE';
Update FII_COM_CC_MAPPINGS1_GT dim
Set dim.parent_manager_id =
NVL((select mgr.mgr_id
from FII_CC_MGR_HIER_GT mgr
where mgr.mgr_level = G_AGGREGATION_LEVELS
and mgr.emp_id = dim.manager_id), dim.manager_id);
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS1_GT');
g_phase := 'Update parent_lob_id by INCREMENTAL_LOAD_LOB_MERGE';
Update FII_COM_CC_MAPPINGS1_GT dim
Set dim.parent_lob_id =
NVL((select v.parent_lob_id
from (select flob.parent_lob_id, flob.child_lob_id, lob.child_level
from fii_full_lob_hiers flob,
fii_lob_hierarchies lob
where lob.child_lob_id = flob.parent_lob_id
order by lob.child_level DESC) v
where v.child_lob_id = dim.lob_id
and rownum = 1), dim.lob_id);
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS1_GT');
(select COMPANY_COST_CENTER_ORG_ID ,
COST_CENTER_ID ,
COMPANY_ID ,
MANAGER_ID ,
VALID_MGR_FLAG ,
LOB_ID,
PARENT_MANAGER_ID,
PARENT_LOB_ID
from FII_COM_CC_MAPPINGS1_GT
minus
select COMPANY_COST_CENTER_ORG_ID ,
COST_CENTER_ID ,
COMPANY_ID ,
MANAGER_ID ,
VALID_MGR_FLAG ,
LOB_ID,
PARENT_MANAGER_ID,
PARENT_LOB_ID
from FII_COM_CC_MAPPINGS
) mappt
ON
( -- mapp.COST_CENTER_ID = mappt.COST_CENTER_ID and
-- mapp.company_id = mappt.company_id
mapp.COMPANY_COST_CENTER_ORG_ID = mappt.COMPANY_COST_CENTER_ORG_ID
)
when matched then
update set
-- mapp.COMPANY_COST_CENTER_ORG_ID = mappt.COMPANY_COST_CENTER_ORG_ID,
mapp.COST_CENTER_ID = mappt.COST_CENTER_ID,
mapp.company_id = mappt.company_id,
mapp.MANAGER_ID = mappt.MANAGER_ID ,
mapp.VALID_MGR_FLAG = mappt.VALID_MGR_FLAG ,
mapp.LOB_ID = mappt.LOB_ID,
mapp.PARENT_MANAGER_ID = mappt.PARENT_MANAGER_ID,
mapp.PARENT_LOB_ID = mappt.PARENT_LOB_ID,
mapp.LAST_UPDATE_DATE = sysdate,
mapp.LAST_UPDATED_BY = g_fii_user_id,
mapp.LAST_UPDATE_LOGIN = g_fii_login_id
when not matched then
insert (
mapp.COMPANY_COST_CENTER_ORG_ID ,
mapp.COST_CENTER_ID ,
mapp.COMPANY_ID ,
mapp.MANAGER_ID ,
mapp.VALID_MGR_FLAG ,
mapp.LOB_ID,
mapp.PARENT_MANAGER_ID,
mapp.PARENT_LOB_ID,
mapp.CREATION_DATE ,
mapp.CREATED_BY ,
mapp.LAST_UPDATE_DATE ,
mapp.LAST_UPDATED_BY ,
mapp.LAST_UPDATE_LOGIN)
values
(
mappt.COMPANY_COST_CENTER_ORG_ID ,
mappt.COST_CENTER_ID ,
mappt.COMPANY_ID ,
mappt.MANAGER_ID ,
mappt.VALID_MGR_FLAG ,
mappt.LOB_ID,
mappt.PARENT_MANAGER_ID,
mappt.PARENT_LOB_ID,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id);
select FLEX_VALUE_SET_ID into l_vset_id
from fnd_flex_value_sets
where flex_value_set_name = 'Financials Intelligence Internal Value Set';
select flex_value_id into G_UNASSIGNED_LOB_ID
from fnd_flex_values
where flex_value_set_id = l_vset_id
and flex_value = 'UNASSIGNED';
SELECT 1 INTO l_flag
FROM fii_lob_assignments
where rownum = 1;
select fv1.flex_value_set_id company_vs_id,
fv2.flex_value_set_id cost_center_vs_id
from FII_COM_CC_MAPPINGS_GT ccc,
fnd_flex_values fv1,
fnd_flex_values fv2
where ccc.coa_id = -1
and ccc.company_id = fv1.flex_value_id
and ccc.cost_center_id = fv2.flex_value_id
for update of ccc.coa_id;
select coa.coa_id into l_coa_id
from
(select ID_FLEX_NUM coa_id
from fnd_id_flex_segments
where APPLICATION_ID = 101
and ID_FLEX_CODE = 'GL#'
and FLEX_VALUE_SET_ID = l_com_vs_id
intersect
select ID_FLEX_NUM coa_id
from fnd_id_flex_segments
where APPLICATION_ID = 101
and ID_FLEX_CODE = 'GL#'
and FLEX_VALUE_SET_ID = l_cc_vs_id
intersect
select CHART_OF_ACCOUNTS_ID coa_id
from fii_dim_mapping_rules
where DIMENSION_SHORT_NAME = 'FII_LOB') coa
where rownum = 1;
update FII_COM_CC_MAPPINGS_GT
set coa_id = l_coa_id
where current of c_all_value_sets;
fii_util.put_line('Updated '||l_count||' rows with coa_id = -1 in FII_COM_CC_MAPPINGS_GT');
select count(*) cnt,
company_id,
cost_center_id
from fii_ccc_mgr_gt
where company_id is not null
and cost_center_id is not null
group by company_id, cost_center_id
having count(*) > 1;
select com.flex_value company,
cc.flex_value cost_center,
org.name organization
from fii_ccc_mgr_gt gt,
hr_all_organization_units org,
fnd_flex_values com,
fnd_flex_values cc
where gt.company_id = p_com_id
and gt.cost_center_id = p_cc_id
and gt.ccc_org_id = org.organization_id
and gt.company_id = com.flex_value_id
and gt.cost_center_id = cc.flex_value_id;
INSERT INTO FII_COM_CC_MAPPINGS_GT
(COMPANY_COST_CENTER_ORG_ID,
COMPANY_ID,
COST_CENTER_ID,
COA_ID)
select NVL(max(ccc_org_id), -1),
company_id,
cost_center_id,
-1
from fii_ccc_mgr_gt
where company_id is not null
and cost_center_id is not null
group by company_id, cost_center_id
having count(*) = 1;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_CC_MAPPINGS_GT');
select 1 into l_count
from FII_COM_CC_MAPPINGS_GT
where company_cost_center_org_id = -1
and rownum = 1;
g_phase := 'insert into FII_ORG_MGR_MAPPINGS';
INSERT /*+ APPEND */ INTO FII_ORG_MGR_MAPPINGS (
manager_id,
ccc_org_id,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN)
SELECT x.mgr_id,
company_cost_center_org_id,
SYSDATE,
g_fii_user_id,
SYSDATE,
g_fii_user_id,
g_fii_login_id
FROM fii_com_cc_mappings,
(SELECT DISTINCT emp_id,
mgr_id
FROM fii_cc_mgr_hierarchies) x
WHERE x.emp_id = parent_manager_id;
fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows to FII_ORG_MGR_MAPPINGS');
select 1 into l_count from FII_CC_MGR_HIER_GT
where rownum = 1;
FII_CC_MGR_SUP_C.Incre_Update (errbuf, retcode);