[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE gcs_entity_cctr_orgs WHERE entity_id = p_entity_id;
INSERT INTO gcs_entity_cctr_orgs
(entity_id,
company_cost_center_org_id,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT p_entity_id,
coa.company_cost_center_org_id,
1,
sysdate,
eo.created_by,
sysdate,
eo.last_updated_by,
eo.last_update_login
FROM gcs_entity_organizations eo,
fem_cctr_orgs_attr coa,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb
WHERE eo.entity_id = p_entity_id
AND coa.dim_attribute_numeric_member =
eo.company_cost_center_org_id
AND coa.attribute_id = fdab.attribute_id
AND coa.version_id = fdavb.version_id
AND fdab.attribute_varchar_label = 'COMPANY'
AND fdavb.attribute_id = fdab.attribute_id
AND fdavb.default_version_flag = 'Y';
SELECT gl_sets_of_books_s.nextval INTO new_ledger_id FROM dual;
SELECT xml_data
INTO l_local_clob
FROM gcs_xml_files xf
WHERE xf.xml_file_id = p_file_id
AND xf.xml_file_type = 'ENTITY_LOADER'
AND xf.language = 'US';
INSERT INTO fem_entities_vl
(entity_id,
entity_display_code,
entity_name,
description,
value_set_id,
enabled_flag,
read_only_flag,
personal_flag,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(new_entity_id,
to_char(new_entity_id),
entity_name,
entity_desc,
18,
'Y',
'N',
'N',
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO gcs_entity_organizations
(entity_id,
company_cost_center_org_id,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(new_entity_id,
entity_comp_id,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_numeric_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(base_org_attr_id,
base_org_v_id,
new_entity_id,
18,
'N',
entity_base_org_id,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_varchar_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(entity_type_attr_id,
entity_type_v_id,
new_entity_id,
18,
'N',
entity_type_code,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
varchar_assign_value,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(contact_attr_id,
contact_v_id,
new_entity_id,
18,
'N',
entity_contact,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
varchar_assign_value,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(logo_attr_id,
logo_v_id,
new_entity_id,
18,
'N',
entity_logo,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_numeric_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(ledger_attr_id,
ledger_v_id,
new_entity_id,
18,
'N',
entity_ledger_id,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_numeric_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(trs_attr_id,
trs_v_id,
new_entity_id,
18,
'N',
entity_trs_obj_id,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_numeric_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(vrs_attr_id,
vrs_v_id,
new_entity_id,
18,
'N',
entity_vrs_obj_id,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_numeric_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(ledger_attr_id,
ledger_v_id,
new_entity_id,
18,
'N',
entity_ledger_id,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_numeric_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(bal_rule_attr_id,
bal_rule_v_id,
new_entity_id,
18,
'N',
entity_bal_rule_id,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
SELECT FND_FLEX_VALUES_S.nextval INTO elim_entity_id FROM dual;
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_numeric_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(elim_attr_id,
elim_v_id,
new_entity_id,
18,
'N',
elim_entity_id,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_vl
(entity_id,
entity_display_code,
entity_name,
description,
value_set_id,
enabled_flag,
read_only_flag,
personal_flag,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(elim_entity_id,
to_char(elim_entity_id),
entity_elim_name,
entity_elim_name,
18,
'Y',
'N',
'N',
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_varchar_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(entity_type_attr_id,
entity_type_v_id,
elim_entity_id,
18,
'N',
'E',
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_numeric_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(src_sys_attr_id,
src_sys_v_id,
elim_entity_id,
18,
'N',
70,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
varchar_assign_value,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(contact_attr_id,
contact_v_id,
elim_entity_id,
18,
'N',
entity_contact,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
varchar_assign_value,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(logo_attr_id,
logo_v_id,
elim_entity_id,
18,
'N',
entity_logo,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_varchar_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(recon_leaf_attr_id,
recon_leaf_v_id,
elim_entity_id,
18,
'N',
'N',
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_numeric_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(cont_attr_id,
cont_v_id,
new_entity_id,
18,
'N',
entity_cont_id,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_numeric_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(src_sys_attr_id,
src_sys_v_id,
new_entity_id,
18,
'N',
entity_src_sys,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_varchar_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(secure_attr_id,
secure_v_id,
new_entity_id,
18,
'N',
entity_sec_by_role,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_varchar_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(secure_attr_id,
secure_v_id,
elim_entity_id,
18,
'N',
entity_sec_by_role,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
INSERT INTO gcs_role_entity_relns
(role_name,
orig_system,
orig_system_id,
partition_id,
entity_id,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT wlr.name,
wlr.orig_system,
wlr.orig_system_id,
wlr.partition_id,
load_entities.new_entity_id,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id
FROM wf_local_roles wlr
WHERE wlr.name = entity_role_name;
INSERT INTO gcs_role_entity_relns
(role_name,
orig_system,
orig_system_id,
partition_id,
entity_id,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT wlr.name,
wlr.orig_system,
wlr.orig_system_id,
wlr.partition_id,
load_entities.elim_entity_id,
1,
sysdate,
user_id,
sysdate,
user_id,
login_id
FROM wf_local_roles wlr
WHERE wlr.name = entity_role_name;
INSERT INTO fem_entities_attr
(attribute_id,
version_id,
entity_id,
value_set_id,
aw_snapshot_flag,
dim_attribute_varchar_member,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(recon_leaf_attr_id,
recon_leaf_v_id,
new_entity_id,
18,
'N',
'N',
1,
sysdate,
user_id,
sysdate,
user_id,
login_id);
/***** Logic to insert the ACTUAL/ADB rows into GCS_ENTITIES_ATTR
for all operating entities********/
--Start - Code inserted by Santosh Matam Dated 16-jan-2006
IF read_mode IN (1, 2) THEN
--Insert one row for Actuals
INSERT INTO gcs_entities_attr
(entity_id,
data_type_code,
ledger_id,
source_system_code,
balances_rule_id,
transform_rule_set_id,
validation_rule_set_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
-- Bug fix : 5843592
effective_start_date)
(SELECT feb.entity_id,
'ACTUAL',
fea_ledger.dim_attribute_numeric_member ledger_id,
fea_src.dim_attribute_numeric_member source_system_code,
fea_bal_rule.dim_attribute_numeric_member balances_rule_id,
--Bugfix 5087900
trs.rule_set_id transform_rule_set_id,
vrs.rule_set_id validation_rule_set_id,
sysdate,
user_id,
sysdate,
user_id,
login_id,
1,
-- Bug fix : 5843592
to_date('01-01-1900','dd-MM-yyyy')
FROM fem_entities_b feb,
fem_entities_attr fea_src,
fem_entities_attr fea_ledger,
fem_entities_attr fea_bal_rule,
fem_entities_attr fea_trs,
fem_entities_attr fea_vrs,
gcs_lex_map_rule_sets trs,
gcs_lex_map_rule_sets vrs
WHERE fea_ledger.entity_id = feb.entity_id
AND fea_ledger.attribute_id = ledger_attr_id
AND fea_ledger.version_id = ledger_v_id
AND fea_bal_rule.entity_id(+) = feb.entity_id
AND fea_bal_rule.attribute_id(+) = bal_rule_attr_id
AND fea_bal_rule.version_id(+) = bal_rule_v_id
AND fea_trs.entity_id(+) = feb.entity_id
AND fea_trs.attribute_id(+) = trs_attr_id
AND fea_trs.version_id(+) = trs_v_id
AND fea_vrs.entity_id(+) = feb.entity_id
AND fea_vrs.attribute_id(+) = vrs_attr_id
AND fea_vrs.version_id(+) = vrs_v_id
AND fea_src.entity_id = feb.entity_id
AND fea_src.attribute_id = src_sys_attr_id
AND fea_src.version_id = src_sys_v_id
AND trs.associated_object_id(+) = fea_trs.dim_attribute_numeric_member
AND vrs.associated_object_id(+) = fea_vrs.dim_attribute_numeric_member
AND feb.entity_id = new_entity_id);
INSERT INTO gcs_entities_attr
(entity_id,
data_type_code,
ledger_id,
source_system_code,
balances_rule_id,
transform_rule_set_id,
validation_rule_set_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
-- Bug fix : 5843592
effective_start_date)
(SELECT feb.entity_id,
'ADB',
fea_ledger.dim_attribute_numeric_member ledger_id,
fea_src.dim_attribute_numeric_member source_system_code,
fea_bal_rule.dim_attribute_numeric_member balances_rule_id,
--Bugfix 5087900
trs.rule_set_id transform_rule_set_id,
vrs.rule_set_id validation_rule_set_id,
sysdate,
user_id,
sysdate,
user_id,
login_id,
1,
-- Bug fix : 5843592
to_date('01-01-1900','dd-MM-yyyy')
FROM fem_entities_b feb,
fem_entities_attr fea_src,
fem_entities_attr fea_ledger,
fem_entities_attr fea_bal_rule,
fem_entities_attr fea_trs,
fem_entities_attr fea_vrs,
gcs_lex_map_rule_sets trs,
gcs_lex_map_rule_sets vrs
WHERE fea_ledger.entity_id = feb.entity_id
AND fea_ledger.attribute_id = ledger_attr_id
AND fea_ledger.version_id = ledger_v_id
AND fea_bal_rule.entity_id(+) = feb.entity_id
AND fea_bal_rule.attribute_id(+) = bal_rule_attr_id
AND fea_bal_rule.version_id(+) = bal_rule_v_id
AND fea_trs.entity_id(+) = feb.entity_id
AND fea_trs.attribute_id(+) = trs_attr_id
AND fea_trs.version_id(+) = trs_v_id
AND fea_vrs.entity_id(+) = feb.entity_id
AND fea_vrs.attribute_id(+) = vrs_attr_id
AND fea_vrs.version_id(+) = vrs_v_id
AND fea_src.entity_id = feb.entity_id
AND fea_src.attribute_id = src_sys_attr_id
AND fea_src.version_id = src_sys_v_id
AND trs.associated_object_id(+) = fea_trs.dim_attribute_numeric_member
AND vrs.associated_object_id(+) = fea_vrs.dim_attribute_numeric_member
AND feb.entity_id = new_entity_id);
PROCEDURE Update_Entity_Orgs(x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2) IS
v_module VARCHAR2(30);
v_module := 'Update_Entity_Orgs';
DELETE gcs_entity_cctr_orgs;
INSERT INTO gcs_entity_cctr_orgs
(entity_id,
company_cost_center_org_id,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT eo.entity_id,
coa.company_cost_center_org_id,
1,
sysdate,
eo.created_by,
sysdate,
eo.last_updated_by,
eo.last_update_login
FROM gcs_entity_organizations eo,
fem_cctr_orgs_attr coa,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb
WHERE coa.dim_attribute_numeric_member =
eo.company_cost_center_org_id
AND coa.attribute_id = fdab.attribute_id
AND coa.version_id = fdavb.version_id
AND fdab.attribute_varchar_label = 'COMPANY'
AND fdavb.attribute_id = fdab.attribute_id
AND fdavb.default_version_flag = 'Y';
END Update_Entity_Orgs;