[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(row_id IN OUT NOCOPY VARCHAR2,
hierarchy_id VARCHAR2,
top_entity_id NUMBER,
start_date VARCHAR2,
calendar_id NUMBER,
dimension_group_id NUMBER,
ie_by_org_code VARCHAR2,
balance_by_org_flag VARCHAR2,
enabled_flag VARCHAR2,
threshold_amount NUMBER,
threshold_currency VARCHAR2,
fem_ledger_id NUMBER,
column_name VARCHAR2,
object_version_number NUMBER,
hierarchy_name VARCHAR2,
description VARCHAR2,
last_update_date DATE,
last_updated_by NUMBER,
last_update_login NUMBER,
creation_date DATE,
created_by NUMBER) IS
CURSOR hier_row IS
SELECT rowid
FROM gcs_hierarchies_b hb
WHERE hb.hierarchy_id = insert_row.hierarchy_id;
INSERT INTO gcs_hierarchies_b
(hierarchy_id,
top_entity_id,
start_date,
calendar_id,
dimension_group_id,
ie_by_org_code,
balance_by_org_flag,
enabled_flag,
threshold_amount,
threshold_currency,
fem_ledger_id,
column_name,
object_version_number,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
SELECT hierarchy_id,
top_entity_id,
start_date,
calendar_id,
dimension_group_id,
ie_by_org_code,
balance_by_org_flag,
enabled_flag,
threshold_amount,
threshold_currency,
fem_ledger_id,
column_name,
object_version_number,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM gcs_hierarchies_b hb
WHERE hb.hierarchy_id = insert_row.hierarchy_id);
INSERT INTO gcs_hierarchies_tl
(hierarchy_id,
language,
source_lang,
hierarchy_name,
description,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
SELECT hierarchy_id,
userenv('LANG'),
userenv('LANG'),
hierarchy_name,
description,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM gcs_hierarchies_tl htl
WHERE htl.hierarchy_id = insert_row.hierarchy_id
AND htl.language = userenv('LANG'));
END Insert_Row;
PROCEDURE Update_Row(hierarchy_id VARCHAR2,
top_entity_id NUMBER,
start_date VARCHAR2,
calendar_id NUMBER,
dimension_group_id NUMBER,
ie_by_org_code VARCHAR2,
balance_by_org_flag VARCHAR2,
enabled_flag VARCHAR2,
threshold_amount NUMBER,
threshold_currency VARCHAR2,
fem_ledger_id NUMBER,
column_name VARCHAR2,
object_version_number NUMBER,
hierarchy_name VARCHAR2,
description VARCHAR2,
last_update_date DATE,
last_updated_by NUMBER,
last_update_login NUMBER,
creation_date DATE,
created_by NUMBER) IS
BEGIN
UPDATE gcs_hierarchies_b hb
SET top_entity_id = update_row.top_entity_id,
start_date = update_row.start_date,
calendar_id = update_row.calendar_id,
dimension_group_id = update_row.dimension_group_id,
ie_by_org_code = update_row.ie_by_org_code,
balance_by_org_flag = update_row.balance_by_org_flag,
enabled_flag = update_row.enabled_flag,
threshold_amount = update_row.threshold_amount,
threshold_currency = update_row.threshold_currency,
fem_ledger_id = update_row.fem_ledger_id,
column_name = update_row.column_name,
object_version_number = update_row.object_version_number,
last_update_date = update_row.last_update_date,
last_updated_by = update_row.last_updated_by,
last_update_login = update_row.last_update_login
WHERE hb.hierarchy_id = update_row.hierarchy_id;
INSERT INTO gcs_hierarchies_tl
(hierarchy_id,
language,
source_lang,
hierarchy_name,
description,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
SELECT hierarchy_id,
userenv('LANG'),
userenv('LANG'),
hierarchy_name,
description,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM gcs_hierarchies_tl htl
WHERE htl.hierarchy_id = update_row.hierarchy_id
AND htl.language = userenv('LANG'));
UPDATE gcs_hierarchies_tl ht
SET hierarchy_name = update_row.hierarchy_name,
description = update_row.description,
last_update_date = update_row.last_update_date,
last_updated_by = update_row.last_updated_by,
last_update_login = update_row.last_update_login
WHERE ht.hierarchy_id = update_row.hierarchy_id
AND ht.language = userenv('LANG');
END Update_Row;
last_update_date VARCHAR2,
custom_mode VARCHAR2,
top_entity_id NUMBER,
start_date VARCHAR2,
calendar_id NUMBER,
dimension_group_id NUMBER,
ie_by_org_code VARCHAR2,
balance_by_org_flag VARCHAR2,
enabled_flag VARCHAR2,
threshold_amount NUMBER,
threshold_currency VARCHAR2,
fem_ledger_id NUMBER,
column_name VARCHAR2,
object_version_number NUMBER,
hierarchy_name VARCHAR2,
description VARCHAR2) IS
row_id VARCHAR2(64);
f_ludate DATE; -- entity update date in file
db_ludate DATE; -- entity update date in db
f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
SELECT hb.last_updated_by, hb.last_update_date
INTO db_luby, db_ludate
FROM GCS_HIERARCHIES_B hb
WHERE hb.hierarchy_id = load_row.hierarchy_id;
update_row(hierarchy_id => HIERARCHY_ID,
top_entity_id => TOP_ENTITY_ID,
start_date => F_START_DATE,
calendar_id => CALENDAR_ID,
dimension_group_id => DIMENSION_GROUP_ID,
ie_by_org_code => IE_BY_ORG_CODE,
balance_by_org_flag => BALANCE_BY_ORG_FLAG,
enabled_flag => ENABLED_FLAG,
threshold_amount => THRESHOLD_AMOUNT,
threshold_currency => THRESHOLD_CURRENCY,
fem_ledger_id => FEM_LEDGER_ID,
column_name => COLUMN_NAME,
object_version_number => OBJECT_VERSION_NUMBER,
hierarchy_name => HIERARCHY_NAME,
description => DESCRIPTION,
last_update_date => f_ludate,
last_updated_by => f_luby,
last_update_login => 0,
creation_date => f_ludate,
created_by => f_luby);
insert_row(row_id => row_id,
hierarchy_id => HIERARCHY_ID,
top_entity_id => TOP_ENTITY_ID,
start_date => F_START_DATE,
calendar_id => CALENDAR_ID,
dimension_group_id => DIMENSION_GROUP_ID,
ie_by_org_code => IE_BY_ORG_CODE,
balance_by_org_flag => BALANCE_BY_ORG_FLAG,
enabled_flag => ENABLED_FLAG,
threshold_amount => THRESHOLD_AMOUNT,
threshold_currency => THRESHOLD_CURRENCY,
fem_ledger_id => FEM_LEDGER_ID,
column_name => COLUMN_NAME,
object_version_number => OBJECT_VERSION_NUMBER,
hierarchy_name => HIERARCHY_NAME,
description => DESCRIPTION,
last_update_date => f_ludate,
last_updated_by => f_luby,
last_update_login => 0,
creation_date => f_ludate,
created_by => f_luby);
last_update_date VARCHAR2,
custom_mode VARCHAR2,
hierarchy_name VARCHAR2,
description VARCHAR2) IS
f_luby NUMBER; -- entity owner in file
f_ludate DATE; -- entity update date in file
db_ludate DATE; -- entity update date in db
f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
SELECT htl.last_updated_by, htl.last_update_date
INTO db_luby, db_ludate
FROM GCS_HIERARCHIES_TL htl
WHERE htl.hierarchy_id = translate_row.hierarchy_id
AND htl.language = userenv('LANG');
UPDATE gcs_hierarchies_tl htl
SET hierarchy_name = translate_row.hierarchy_name,
description = translate_row.description,
source_lang = userenv('LANG'),
last_update_date = f_ludate,
last_updated_by = f_luby,
last_update_login = 0
WHERE htl.hierarchy_id = translate_row.hierarchy_id
AND userenv('LANG') IN (htl.language, htl.source_lang);
insert /*+ append parallel(tt) */
into GCS_HIERARCHIES_TL tt
(HIERARCHY_ID,
LANGUAGE,
SOURCE_LANG,
HIERARCHY_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
DESCRIPTION)
select /*+ parallel(v) parallel(t) use_nl(t) */
v.*
from (SELECT /*+ no_merge ordered parellel(b) */
B.HIERARCHY_ID,
L.LANGUAGE_CODE,
B.SOURCE_LANG,
B.HIERARCHY_NAME,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.DESCRIPTION
from GCS_HIERARCHIES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')) v,
GCS_HIERARCHIES_TL t
where T.HIERARCHY_ID(+) = v.HIERARCHY_ID
and T.LANGUAGE(+) = v.LANGUAGE_CODE
and t.HIERARCHY_ID IS NULL;
SELECT r.cons_relationship_id,
r.start_date,
r.end_date,
r.delta_owned
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = p_hierarchy_id
AND r.parent_entity_id = p_parent_entity_id
AND r.child_entity_id = p_original_entity_id
AND r.actual_ownership_flag = 'N'
AND r.start_date <= nvl(r.end_date, r.start_date)
AND r.start_date <= nvl(p_end_date, r.start_date)
AND nvl(r.end_date, p_start_date) >= p_start_date
ORDER BY r.start_date;
SELECT r.parent_entity_id,
r.ownership_percent,
decode(sign(r.start_date - p_start_date),
1,
r.start_date,
p_start_date) start_date,
decode(r.end_date,
null,
p_end_date,
decode(p_end_date,
null,
r.end_date,
decode(sign(r.end_date - p_end_date),
1,
p_end_date,
r.end_date))) end_date
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = p_hierarchy_id
AND r.child_entity_id = p_current_entity_id
AND r.actual_ownership_flag = 'Y'
AND r.start_date <= nvl(r.end_date, r.start_date)
AND p_start_date <= nvl(r.end_date, p_start_date)
AND nvl(p_end_date, r.start_date) >= r.start_date;
INSERT INTO gcs_cons_relationships
(cons_relationship_id,
hierarchy_id,
parent_entity_id,
child_entity_id,
ownership_percent,
start_date,
treatment_id,
curr_treatment_id,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
end_date,
delta_owned,
dominant_parent_flag,
actual_ownership_flag)
VALUES
(gcs_cons_relationships_s.nextval,
p_hierarchy_id,
p_parent_entity_id,
p_original_entity_id,
0,
delta_row.start_date,
null,
null,
1,
sysdate,
fnd_user_id,
sysdate,
fnd_user_id,
fnd_login_id,
p_start_date - 1,
delta_row.delta_owned,
'N',
'N');
UPDATE gcs_cons_relationships r
SET start_date = p_start_date
WHERE r.cons_relationship_id = delta_row.cons_relationship_id;
INSERT INTO gcs_cons_relationships
(cons_relationship_id,
hierarchy_id,
parent_entity_id,
child_entity_id,
ownership_percent,
start_date,
treatment_id,
curr_treatment_id,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
end_date,
delta_owned,
dominant_parent_flag,
actual_ownership_flag)
VALUES
(gcs_cons_relationships_s.nextval,
p_hierarchy_id,
p_parent_entity_id,
p_original_entity_id,
0,
p_end_date + 1,
null,
null,
1,
sysdate,
fnd_user_id,
sysdate,
fnd_user_id,
fnd_login_id,
delta_row.end_date,
delta_row.delta_owned,
'N',
'N');
UPDATE gcs_cons_relationships r
SET end_date = p_end_date
WHERE r.cons_relationship_id = delta_row.cons_relationship_id;
INSERT INTO gcs_cons_relationships
(cons_relationship_id,
hierarchy_id,
parent_entity_id,
child_entity_id,
ownership_percent,
start_date,
treatment_id,
curr_treatment_id,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
end_date,
delta_owned,
dominant_parent_flag,
actual_ownership_flag)
VALUES
(gcs_cons_relationships_s.nextval,
p_hierarchy_id,
p_parent_entity_id,
p_original_entity_id,
0,
last_end_date + 1,
null,
null,
1,
sysdate,
fnd_user_id,
sysdate,
fnd_user_id,
fnd_login_id,
delta_row.start_date - 1,
p_effective_ownership,
'N',
'N');
UPDATE gcs_cons_relationships r
SET r.delta_owned = r.delta_owned + p_effective_ownership
WHERE r.cons_relationship_id = delta_row.cons_relationship_id;
INSERT INTO gcs_cons_relationships
(cons_relationship_id,
hierarchy_id,
parent_entity_id,
child_entity_id,
ownership_percent,
start_date,
treatment_id,
curr_treatment_id,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
end_date,
delta_owned,
dominant_parent_flag,
actual_ownership_flag)
VALUES
(gcs_cons_relationships_s.nextval,
p_hierarchy_id,
p_parent_entity_id,
p_original_entity_id,
0,
last_end_date + 1,
null,
null,
1,
sysdate,
fnd_user_id,
sysdate,
fnd_user_id,
fnd_login_id,
p_end_date,
p_effective_ownership,
'N',
'N');
SELECT r.parent_entity_id,
r.ownership_percent,
decode(sign(r.start_date - p_start_date),
1,
r.start_date,
p_start_date) start_date,
decode(r.end_date,
null,
p_end_date,
decode(p_end_date,
null,
r.end_date,
decode(sign(r.end_date - p_end_date),
1,
p_end_date,
r.end_date))) end_date
FROM gcs_cons_relationships r, gcs_treatments_b tb
WHERE r.hierarchy_id = p_hierarchy_id
AND r.child_entity_id = p_original_entity_id
AND r.actual_ownership_flag = 'Y'
AND r.start_date <= nvl(r.end_date, r.start_date)
AND p_start_date <= nvl(r.end_date, p_start_date)
AND nvl(p_end_date, r.start_date) >= r.start_date
AND tb.treatment_id = r.treatment_id
AND tb.consolidation_type_code = 'NONE';
SELECT r.parent_entity_id,
decode(sign(r.start_date - p_start_date),
1,
r.start_date,
p_start_date) start_date,
decode(r.end_date,
null,
p_end_date,
decode(p_end_date,
null,
r.end_date,
decode(sign(r.end_date - p_end_date),
1,
p_end_date,
r.end_date))) end_date
FROM gcs_cons_relationships r, gcs_treatments_b tb
WHERE r.hierarchy_id = p_hierarchy_id
AND r.child_entity_id = p_parent_entity_id
AND r.actual_ownership_flag = 'Y'
AND r.start_date <= nvl(r.end_date, r.start_date)
AND p_start_date <= nvl(r.end_date, p_start_date)
AND nvl(p_end_date, r.start_date) >= r.start_date
AND tb.treatment_id = r.treatment_id
AND tb.consolidation_type_code = 'FULL';
SELECT r.parent_entity_id,
decode(sign(r.start_date - p_effective_date),
1,
r.start_date,
p_effective_date) start_date,
r.end_date
FROM gcs_cons_relationships r, gcs_treatments_b tb
WHERE r.hierarchy_id = p_hierarchy_id
AND r.child_entity_id = p_child_entity_id
AND r.actual_ownership_flag = 'Y'
AND r.start_date <= nvl(r.end_date, r.start_date)
AND p_effective_date <= nvl(r.end_date, p_effective_date)
AND tb.treatment_id = r.treatment_id
AND tb.consolidation_type_code = 'FULL';
SELECT r.child_entity_id
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = p_hierarchy_id
AND r.parent_entity_id = p_child_entity_id
AND r.actual_ownership_flag = 'Y'
AND r.start_date <= nvl(r.end_date, r.start_date)
AND p_effective_date <= nvl(r.end_date, p_effective_date)
AND r.treatment_id IS NOT NULL;
UPDATE gcs_cons_relationships
SET end_date = p_effective_date - 1
WHERE hierarchy_id = p_hierarchy_id
AND child_entity_id = p_child_entity_id
AND actual_ownership_flag = 'N'
AND nvl(end_date, p_effective_date) >= p_effective_date;
SELECT r.parent_entity_id,
decode(sign(r.start_date - p_start_date),
1,
r.start_date,
p_start_date) start_date,
decode(r.end_date,
null,
p_end_date,
decode(p_end_date,
null,
r.end_date,
decode(sign(r.end_date - p_end_date),
1,
p_end_date,
r.end_date))) end_date
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = p_hierarchy_id
AND r.child_entity_id = p_parent_id
AND r.actual_ownership_flag = 'Y'
AND r.start_date <= nvl(r.end_date, r.start_date)
AND p_start_date <= nvl(r.end_date, p_start_date)
AND nvl(p_end_date, r.start_date) >= r.start_date;
SELECT cr.curr_treatment_id
FROM gcs_cons_eng_runs r,
gcs_cons_eng_run_dtls rd,
fem_cal_periods_attr cpa,
gcs_cons_relationships cr,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb
WHERE r.hierarchy_id = p_hierarchy_id
AND r.run_entity_id = p_parent_id
AND rd.run_name = r.run_name
AND rd.consolidation_entity_id = p_parent_id
AND rd.child_entity_id = p_child_id
AND fdab.attribute_varchar_label = 'CAL_PERIOD_END_DATE'
AND fdavb.attribute_id = fdab.attribute_id
AND fdavb.default_version_flag = 'Y'
AND cpa.cal_period_id = r.cal_period_id
AND cpa.attribute_id = fdab.attribute_id
AND cpa.version_id = fdavb.version_id
AND cpa.date_assign_value < p_date
AND cr.cons_relationship_id = rd.cons_relationship_id
order by cpa.date_assign_value, rd.last_update_date desc;
SELECT ctb.curr_treatment_id
FROM gcs_curr_treatments_b ctb
WHERE ctb.enabled_flag = 'Y'
ORDER BY decode(ctb.default_flag, 'Y', 0, 1), ctb.curr_treatment_id;
SELECT currency_code
INTO to_ccy
FROM gcs_entity_cons_attrs
WHERE hierarchy_id = p_hierarchy_id
AND entity_id = p_parent_id;
SELECT currency_code
INTO from_ccy
FROM gcs_entity_cons_attrs
WHERE hierarchy_id = p_hierarchy_id
AND entity_id = p_child_id;
SELECT r.*
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = l_hierarchy_id
AND r.child_entity_id = l_child_id
AND r.parent_entity_id <> l_parent_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND l_start_date <= nvl(r.end_date, l_start_date)
AND r.actual_ownership_flag = 'Y'
AND r.dominant_parent_flag = 'Y';
SELECT r.*
FROM gcs_cons_relationships r, gcs_treatments_b tb
WHERE r.hierarchy_id = l_hierarchy_id
AND r.child_entity_id = l_child_id
AND r.parent_entity_id <> l_parent_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND l_start_date <= r.start_date
AND r.actual_ownership_flag = 'Y'
AND tb.treatment_id = r.treatment_id
AND tb.consolidation_type_code = 'FULL'
ORDER BY r.start_date;
SELECT r.cons_relationship_id
FROM gcs_cons_relationships r, gcs_treatments_b tb
WHERE r.hierarchy_id = l_hierarchy_id
AND r.child_entity_id = l_child_id
AND r.parent_entity_id <> l_parent_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND r.start_date < l_start_date
AND l_start_date <= nvl(r.end_date, l_start_date)
AND r.actual_ownership_flag = 'Y'
AND r.dominant_parent_flag = 'Y'
AND tb.treatment_id = r.treatment_id
AND tb.consolidation_type_code = 'NONE';
SELECT r.*
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = l_hierarchy_id
AND r.child_entity_id = l_child_id
AND r.parent_entity_id <> l_parent_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND l_start_date <= nvl(r.end_date, l_start_date)
AND r.actual_ownership_flag = 'Y'
ORDER BY r.start_date;
SELECT r.*
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = l_hierarchy_id
AND r.child_entity_id = l_child_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND l_start_date <= nvl(r.end_date, l_start_date)
AND r.actual_ownership_flag = 'Y'
AND r.dominant_parent_flag = 'Y'
ORDER BY r.start_date;
SELECT r.hierarchy_id,
r.parent_entity_id,
r.child_entity_id,
r.ownership_percent,
r.start_date,
r.treatment_id,
r.curr_treatment_id,
r.dominant_parent_flag,
tb.consolidation_type_code
INTO l_hierarchy_id,
l_parent_id,
l_child_id,
l_ownership,
l_start_date,
l_treat_id,
l_ccy_treat_id,
l_dominant_flag,
l_treat_type
FROM gcs_cons_relationships r, gcs_treatments_b tb
WHERE r.cons_relationship_id = p_rel_id
AND tb.treatment_id = r.treatment_id;
UPDATE gcs_cons_relationships r
SET dominant_parent_flag = 'Y'
WHERE r.cons_relationship_id = p_rel_id;
INSERT INTO gcs_cons_relationships
(CONS_RELATIONSHIP_ID,
HIERARCHY_ID,
PARENT_ENTITY_ID,
CHILD_ENTITY_ID,
OWNERSHIP_PERCENT,
START_DATE,
TREATMENT_ID,
CURR_TREATMENT_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
DOMINANT_PARENT_FLAG,
ACTUAL_OWNERSHIP_FLAG)
VALUES
(gcs_cons_relationships_s.nextval,
other_row.hierarchy_id,
other_row.parent_entity_id,
other_row.child_entity_id,
other_row.ownership_percent,
l_start_date,
other_row.treatment_id,
null,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
other_row.end_date,
'N',
other_row.actual_ownership_flag);
UPDATE gcs_cons_relationships r
SET end_date = l_start_date - 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE r.cons_relationship_id = other_row.cons_relationship_id;
UPDATE gcs_cons_relationships r
SET dominant_parent_flag = 'N',
curr_treatment_id = null,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE r.cons_relationship_id = other_row.cons_relationship_id;
UPDATE gcs_cons_relationships r
SET end_date = future_full_row.start_date - 1
WHERE r.hierarchy_id = l_hierarchy_id
AND r.parent_entity_id = l_parent_id
AND r.child_entity_id = l_child_id
AND r.end_date IS NULL
AND r.actual_ownership_flag = 'Y';
INSERT INTO gcs_cons_relationships
(CONS_RELATIONSHIP_ID,
HIERARCHY_ID,
PARENT_ENTITY_ID,
CHILD_ENTITY_ID,
OWNERSHIP_PERCENT,
START_DATE,
TREATMENT_ID,
CURR_TREATMENT_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
DOMINANT_PARENT_FLAG,
ACTUAL_OWNERSHIP_FLAG)
VALUES
(gcs_cons_relationships_s.nextval,
l_hierarchy_id,
l_parent_id,
l_child_id,
l_ownership,
future_full_row.start_date,
l_treat_id,
null,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
future_full_row.end_date,
'N',
'Y');
INSERT INTO gcs_cons_relationships
(CONS_RELATIONSHIP_ID,
HIERARCHY_ID,
PARENT_ENTITY_ID,
CHILD_ENTITY_ID,
OWNERSHIP_PERCENT,
START_DATE,
TREATMENT_ID,
CURR_TREATMENT_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
DOMINANT_PARENT_FLAG,
ACTUAL_OWNERSHIP_FLAG)
VALUES
(gcs_cons_relationships_s.nextval,
l_hierarchy_id,
l_parent_id,
l_child_id,
l_ownership,
future_full_row.end_date + 1,
l_treat_id,
l_ccy_treat_id,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
null,
'Y',
'Y');
INSERT INTO gcs_cons_relationships
(CONS_RELATIONSHIP_ID,
HIERARCHY_ID,
PARENT_ENTITY_ID,
CHILD_ENTITY_ID,
OWNERSHIP_PERCENT,
START_DATE,
TREATMENT_ID,
CURR_TREATMENT_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
DOMINANT_PARENT_FLAG,
ACTUAL_OWNERSHIP_FLAG)
SELECT gcs_cons_relationships_s.nextval,
r.hierarchy_id,
r.parent_entity_id,
r.child_entity_id,
r.ownership_percent,
l_start_date,
r.treatment_id,
null,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
r.end_date,
'N',
'Y'
FROM gcs_cons_relationships r
WHERE r.cons_relationship_id = l_temp_rel_id;
UPDATE gcs_cons_relationships r
SET end_date = l_start_date - 1
WHERE r.cons_relationship_id = l_temp_rel_id;
UPDATE gcs_cons_relationships r
SET dominant_parent_flag = 'N', curr_treatment_id = null
WHERE r.hierarchy_id = l_hierarchy_id
AND r.child_entity_id = l_child_id
AND r.parent_entity_id <> l_parent_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND r.start_date >= l_start_date
AND r.actual_ownership_flag = 'Y'
AND r.dominant_parent_flag = 'Y'
AND EXISTS
(SELECT 1
FROM gcs_treatments_b tb
WHERE tb.treatment_id = r.treatment_id
AND tb.consolidation_type_code = 'NONE');
SELECT currency_code
INTO l_from_ccy
FROM gcs_entity_cons_attrs
WHERE hierarchy_id = l_hierarchy_id
AND entity_id = l_child_id;
SELECT currency_code
INTO l_to_ccy
FROM gcs_entity_cons_attrs
WHERE hierarchy_id = l_hierarchy_id
AND entity_id = l_parent_id;
UPDATE gcs_cons_relationships r
SET end_date = l_last_end_date
WHERE r.hierarchy_id = l_hierarchy_id
AND r.parent_entity_id = l_parent_id
AND r.child_entity_id = l_child_id
AND r.end_date IS NULL
AND r.actual_ownership_flag = 'Y';
INSERT INTO gcs_cons_relationships
(CONS_RELATIONSHIP_ID,
HIERARCHY_ID,
PARENT_ENTITY_ID,
CHILD_ENTITY_ID,
OWNERSHIP_PERCENT,
START_DATE,
TREATMENT_ID,
CURR_TREATMENT_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
DOMINANT_PARENT_FLAG,
ACTUAL_OWNERSHIP_FLAG)
VALUES
(gcs_cons_relationships_s.nextval,
l_hierarchy_id,
l_parent_id,
l_child_id,
l_ownership,
l_last_end_date + 1,
l_treat_id,
l_ccy_treat_id,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
future_rel_row.start_date - 1,
'Y',
'Y');
INSERT INTO gcs_cons_relationships
(CONS_RELATIONSHIP_ID,
HIERARCHY_ID,
PARENT_ENTITY_ID,
CHILD_ENTITY_ID,
OWNERSHIP_PERCENT,
START_DATE,
TREATMENT_ID,
CURR_TREATMENT_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
DOMINANT_PARENT_FLAG,
ACTUAL_OWNERSHIP_FLAG)
VALUES
(gcs_cons_relationships_s.nextval,
l_hierarchy_id,
l_parent_id,
l_child_id,
l_ownership,
future_rel_row.start_date,
l_treat_id,
null,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
null,
'N',
'Y');
UPDATE gcs_cons_relationships r
SET end_date = l_last_end_date
WHERE r.hierarchy_id = l_hierarchy_id
AND r.parent_entity_id = l_parent_id
AND r.child_entity_id = l_child_id
AND r.end_date IS NULL
and r.actual_ownership_flag = 'Y';
INSERT INTO gcs_cons_relationships
(CONS_RELATIONSHIP_ID,
HIERARCHY_ID,
PARENT_ENTITY_ID,
CHILD_ENTITY_ID,
OWNERSHIP_PERCENT,
START_DATE,
TREATMENT_ID,
CURR_TREATMENT_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
DOMINANT_PARENT_FLAG,
ACTUAL_OWNERSHIP_FLAG)
VALUES
(gcs_cons_relationships_s.nextval,
l_hierarchy_id,
l_parent_id,
l_child_id,
l_ownership,
l_last_end_date + 1,
l_treat_id,
l_ccy_treat_id,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
null,
'Y',
'Y');
UPDATE gcs_cons_relationships r
SET curr_treatment_id = NULL
WHERE r.hierarchy_id = l_hierarchy_id
AND r.parent_entity_id = l_parent_id
AND r.child_entity_id = l_child_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND r.start_date >= l_start_date
AND r.dominant_parent_flag = 'N'
AND r.actual_ownership_flag = 'Y'
AND r.curr_treatment_id IS NOT NULL;
SELECT r.cons_relationship_id, r.parent_entity_id
INTO l_temp_rel_id, l_temp_parent_id
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = l_hierarchy_id
AND r.child_entity_id = l_child_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND r.start_date = l_last_end_date + 1
AND r.end_date = future_dominant_rel_row.start_date - 1
AND r.actual_ownership_flag = 'Y'
AND r.dominant_parent_flag = 'N'
AND rownum = 1;
UPDATE gcs_cons_relationships r
SET dominant_parent_flag = 'Y',
curr_treatment_id = l_temp_ccy_treat_id
WHERE r.cons_relationship_id = l_temp_rel_id;
SELECT r.cons_relationship_id, r.parent_entity_id
INTO l_temp_rel_id, l_temp_parent_id
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = l_hierarchy_id
AND r.child_entity_id = l_child_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND r.start_date = l_last_end_date + 1
AND r.end_date IS NULL
AND r.actual_ownership_flag = 'Y'
AND r.dominant_parent_flag = 'N'
AND rownum = 1;
UPDATE gcs_cons_relationships r
SET dominant_parent_flag = 'Y',
curr_treatment_id = l_temp_ccy_treat_id
WHERE r.cons_relationship_id = l_temp_rel_id;
SELECT r.*
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = p_hier_id
AND r.child_entity_id = p_child_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND p_start_date <= nvl(r.end_date, p_start_date)
AND r.start_date <= nvl(p_end_date, r.start_date)
AND r.actual_ownership_flag = 'Y'
AND r.dominant_parent_flag = 'Y'
ORDER BY r.start_date;
SELECT r.*
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = p_hier_id
AND r.parent_entity_id = p_child_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND l_temp_date <= nvl(r.end_date, l_temp_date)
AND r.actual_ownership_flag = 'Y';
UPDATE gcs_cons_relationships r
SET end_date = l_last_end_date
WHERE r.hierarchy_id = p_hier_id
AND r.parent_entity_id = p_parent_id
AND r.child_entity_id = p_child_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND r.actual_ownership_flag = 'Y'
AND r.dominant_parent_flag = 'N'
AND ((r.end_date IS NULL AND p_end_date IS NULL) OR
(r.end_date = p_end_date));
INSERT INTO gcs_cons_relationships
(CONS_RELATIONSHIP_ID,
HIERARCHY_ID,
PARENT_ENTITY_ID,
CHILD_ENTITY_ID,
OWNERSHIP_PERCENT,
START_DATE,
TREATMENT_ID,
CURR_TREATMENT_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
DOMINANT_PARENT_FLAG,
ACTUAL_OWNERSHIP_FLAG)
VALUES
(gcs_cons_relationships_s.nextval,
p_hier_id,
p_parent_id,
p_child_id,
p_ownership,
l_last_end_date + 1,
p_treat_id,
l_ccy_treat_id,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
dominant_rel_row.start_date - 1,
'Y',
'Y');
INSERT INTO gcs_cons_relationships
(CONS_RELATIONSHIP_ID,
HIERARCHY_ID,
PARENT_ENTITY_ID,
CHILD_ENTITY_ID,
OWNERSHIP_PERCENT,
START_DATE,
TREATMENT_ID,
CURR_TREATMENT_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
DOMINANT_PARENT_FLAG,
ACTUAL_OWNERSHIP_FLAG)
VALUES
(gcs_cons_relationships_s.nextval,
p_hier_id,
p_parent_id,
p_child_id,
p_ownership,
dominant_rel_row.start_date,
p_treat_id,
null,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_temp_end_date,
'N',
'Y');
UPDATE gcs_cons_relationships r
SET end_date = l_last_end_date
WHERE r.hierarchy_id = p_hier_id
AND r.parent_entity_id = p_parent_id
AND r.child_entity_id = p_child_id
AND r.start_date <= nvl(r.end_date, r.start_date)
AND r.actual_ownership_flag = 'Y'
AND r.dominant_parent_flag = 'N'
AND ((r.end_date IS NULL AND p_end_date IS NULL) OR
(r.end_date = p_end_date));
INSERT INTO gcs_cons_relationships
(CONS_RELATIONSHIP_ID,
HIERARCHY_ID,
PARENT_ENTITY_ID,
CHILD_ENTITY_ID,
OWNERSHIP_PERCENT,
START_DATE,
TREATMENT_ID,
CURR_TREATMENT_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
DOMINANT_PARENT_FLAG,
ACTUAL_OWNERSHIP_FLAG)
VALUES
(gcs_cons_relationships_s.nextval,
p_hier_id,
p_parent_id,
p_child_id,
p_ownership,
l_last_end_date + 1,
p_treat_id,
l_ccy_treat_id,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
p_end_date,
'Y',
'Y');
SELECT r.*
FROM gcs_cons_relationships r
WHERE r.hierarchy_id = p_hier_id
AND r.parent_entity_id =
(SELECT hb.top_entity_id
FROM gcs_hierarchies_b hb
WHERE hb.hierarchy_id = p_hier_id)
AND r.start_date <= nvl(r.end_date, r.start_date)
AND p_removal_date <= nvl(r.end_date, p_removal_date)
AND r.actual_ownership_flag = 'Y';
SELECT r.child_entity_id, r.start_date, r.end_date
FROM gcs_cons_relationships r,
fem_entities_attr fea_type,
fem_dim_attributes_b fdab_type,
fem_dim_attr_versions_b fdavb_type
WHERE r.hierarchy_id = p_hier_id
AND r.start_date > p_removal_date
AND fea_type.entity_id = r.child_entity_id
AND fea_type.attribute_id = fdab_type.attribute_id
AND fea_type.version_id = fdavb_type.version_id
AND fdab_type.attribute_varchar_label = 'ENTITY_TYPE_CODE'
AND fdavb_type.attribute_id = fdab_type.attribute_id
AND fdavb_type.default_version_flag = 'Y'
AND fea_type.dim_attribute_varchar_member = 'C'
AND NOT EXISTS
(SELECT 1
FROM gcs_cons_relationships rassoc
WHERE rassoc.hierarchy_id = p_hier_id
AND rassoc.parent_entity_id = r.child_entity_id
AND rassoc.start_date <= r.start_date
AND (rassoc.end_date IS NULL OR
(r.end_date IS NOT NULL AND
rassoc.end_date >= r.end_date))
AND rassoc.actual_ownership_flag = 'Y'
AND rassoc.treatment_id IS NULL)
ORDER BY r.child_entity_id, r.start_date;
INSERT INTO gcs_cons_relationships
(CONS_RELATIONSHIP_ID,
HIERARCHY_ID,
PARENT_ENTITY_ID,
CHILD_ENTITY_ID,
OWNERSHIP_PERCENT,
START_DATE,
TREATMENT_ID,
CURR_TREATMENT_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
DOMINANT_PARENT_FLAG,
ACTUAL_OWNERSHIP_FLAG)
SELECT gcs_cons_relationships_s.nextval,
p_hier_id,
l_child_id,
fea.DIM_ATTRIBUTE_NUMERIC_MEMBER,
100,
l_start_date,
null,
null,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_end_date,
'Y',
'Y'
FROM fem_entities_attr fea,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb
WHERE fea.entity_id = l_child_id
AND fea.attribute_id = fdab.attribute_id
AND fea.version_id = fdavb.version_id
AND fdab.attribute_varchar_label IN
('OPERATING_ENTITY', 'ELIMINATION_ENTITY')
AND fdavb.attribute_id = fdab.attribute_id
AND fdavb.default_version_flag = 'Y';
PROCEDURE Update_Hierarchies_Datatype(p_data_type_code VARCHAR2) IS
TYPE hier_info_rec_type IS RECORD(
hier_id NUMBER,
hier_name VARCHAR2(150));
SELECT gdtctl.data_type_name,
gdtcb.source_dataset_code,
fda.dim_attribute_varchar_member
INTO l_data_type_name, l_dataset_code, l_fem_balance_type
FROM gcs_data_type_codes_b gdtcb,
gcs_data_type_codes_tl gdtctl,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb,
fem_datasets_attr fda
WHERE gdtcb.source_dataset_code = fda.dataset_code
AND gdtcb.data_type_id = gdtctl.data_type_id
AND gdtctl.language = userenv('LANG')
AND fda.attribute_id = fdab.attribute_id
AND fdab.attribute_id = fdavb.attribute_id
AND fda.version_id = fdavb.version_id
AND fdavb.default_version_flag = 'Y'
AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-DATASET_BALANCE_TYPE_CODE')
AND gdtcb.data_type_code = p_data_type_code;
SELECT fda.dim_attribute_numeric_member
INTO l_budget_id
FROM fem_datasets_attr fda,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb
WHERE fda.attribute_id = fdab.attribute_id
AND fdab.attribute_id = fdavb.attribute_id
AND fda.version_id = fdavb.version_id
AND fdavb.default_version_flag = 'Y'
AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-BUDGET_ID')
AND fda.dataset_code = l_dataset_code;
SELECT fda.dim_attribute_numeric_member
INTO l_encumbrance_type_id
FROM fem_datasets_attr fda,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb
WHERE fda.attribute_id = fdab.attribute_id
AND fdab.attribute_id = fdavb.attribute_id
AND fda.version_id = fdavb.version_id
AND fdavb.default_version_flag = 'Y'
AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-ENCUMBRANCE_TYPE_ID')
AND fda.dataset_code = l_dataset_code;
SELECT hierarchy_id, hierarchy_name BULK COLLECT
INTO l_hier_info
FROM gcs_hierarchies_tl
WHERE language = userenv('LANG');
SELECT dataset_code
INTO l_base_ds_code
FROM fem_datasets_tl
WHERE language = userenv('LANG')
AND dataset_name = l_base_display_code;
INSERT INTO gcs_dataset_codes
(hierarchy_id,
balance_type_code,
dataset_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(l_hier_info(l_counter).hier_id,
l_base_balance_type,
l_base_ds_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
SELECT dataset_code
INTO l_analyze_ds_code
FROM fem_datasets_tl
WHERE language = userenv('LANG')
AND dataset_name = l_analyze_display_code;
INSERT INTO gcs_dataset_codes
(hierarchy_id,
balance_type_code,
dataset_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(l_hier_info(l_counter).hier_id,
l_analyze_balance_type,
l_analyze_ds_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
END Update_Hierarchies_Datatype;
SELECT hierarchy_name
INTO l_hierarchy_name
FROM gcs_hierarchies_tl
WHERE language = userenv('LANG')
AND hierarchy_id = p_hierarchy_id;
SELECT gdtcb.data_type_code,
gdtctl.data_type_name,
gdtcb.source_dataset_code,
fda.dim_attribute_varchar_member BULK COLLECT
INTO l_data_type_info
FROM gcs_data_type_codes_b gdtcb,
gcs_data_type_codes_tl gdtctl,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb,
fem_datasets_attr fda
WHERE gdtcb.source_dataset_code = fda.dataset_code
AND gdtcb.data_type_id = gdtctl.data_type_id
AND gdtctl.language = userenv('LANG')
AND fda.attribute_id = fdab.attribute_id
AND fdab.attribute_id = fdavb.attribute_id
AND fda.version_id = fdavb.version_id
AND fdavb.default_version_flag = 'Y'
AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-DATASET_BALANCE_TYPE_CODE');
SELECT fda.dim_attribute_numeric_member
INTO l_budget_id
FROM fem_datasets_attr fda,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb
WHERE fda.attribute_id = fdab.attribute_id
AND fdab.attribute_id = fdavb.attribute_id
AND fda.version_id = fdavb.version_id
AND fdavb.default_version_flag = 'Y'
AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-BUDGET_ID')
AND fda.dataset_code = l_dataset_code;
SELECT fda.dim_attribute_numeric_member
INTO l_encumbrance_type_id
FROM fem_datasets_attr fda,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb
WHERE fda.attribute_id = fdab.attribute_id
AND fdab.attribute_id = fdavb.attribute_id
AND fda.version_id = fdavb.version_id
AND fdavb.default_version_flag = 'Y'
AND fda.attribute_id = gcs_utility_pkg.get_dimension_attribute('DATASET_CODE-ENCUMBRANCE_TYPE_ID')
AND fda.dataset_code = l_dataset_code;
SELECT dataset_code
INTO l_base_ds_code
FROM fem_datasets_tl
WHERE language = userenv('LANG')
AND dataset_name = l_base_display_code;
INSERT INTO gcs_dataset_codes
(hierarchy_id,
balance_type_code,
dataset_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(p_hierarchy_id,
l_base_balance_type,
l_base_ds_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
SELECT dataset_code
INTO l_analyze_ds_code
FROM fem_datasets_tl
WHERE language = userenv('LANG')
AND dataset_name = l_analyze_display_code;
INSERT INTO gcs_dataset_codes
(hierarchy_id,
balance_type_code,
dataset_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(p_hierarchy_id,
l_analyze_balance_type,
l_analyze_ds_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
SELECT tl.hierarchy_name,
b.fem_ledger_id
INTO l_hierarchy_name,l_ledger_id
FROM gcs_hierarchies_b b,
gcs_hierarchies_tl tl
WHERE b.hierarchy_id = tl.hierarchy_id
AND tl.hierarchy_id = p_hierarchy_id
AND tl.language = userenv('LANG');
SELECT gdc.balance_type_code,
gdc.dataset_code,
gtl.data_type_name BULK COLLECT
INTO l_dataset_info
FROM gcs_dataset_codes gdc,
gcs_data_type_codes_b gtb,
gcs_data_type_codes_tl gtl
WHERE gdc.hierarchy_id = p_hierarchy_id
AND INSTR(gdc.balance_type_code,gtb.data_type_code) > 0
AND gtb.data_type_id = gtl.data_type_id
AND gtl.language = userenv('LANG');
UPDATE fem_datasets_b
SET dataset_display_code = l_display_code
WHERE dataset_code = l_dataset_code;
UPDATE fem_datasets_tl
SET dataset_name = l_display_code,
description = l_description
WHERE dataset_code = l_dataset_code
AND language = userenv('LANG');
UPDATE fem_ledgers_b
SET ledger_display_code = l_ledger_name
WHERE ledger_id = l_ledger_id;
UPDATE fem_ledgers_tl
SET ledger_name = l_ledger_name,
description = l_ledger_desc
WHERE ledger_id = l_ledger_id
AND language = userenv('LANG');