[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gcr.parent_entity_id
FROM gcs_cons_relationships gcr
START WITH child_entity_id = p_entity_id
AND dominant_parent_flag = 'Y'
AND gcr.hierarchy_id = p_hierarchy_id
AND p_end_date BETWEEN gcr.start_date AND
NVL(gcr.end_date, p_end_date)
CONNECT BY PRIOR parent_entity_id = child_entity_id
AND dominant_parent_flag = 'Y'
AND gcr.hierarchy_id = p_hierarchy_id
AND p_end_date BETWEEN gcr.start_date AND
NVL(gcr.end_date, p_end_date);
SELECT count(1)
FROM gcs_cons_relationships gcr,
fem_entities_attr fea,
gcs_entities_attr gea
WHERE gcr.hierarchy_id = p_hierarchy_id
AND gcr.parent_entity_id = p_entity_id
AND gcr.dominant_parent_flag = 'Y'
AND gcr.child_entity_id = fea.entity_id
AND fea.attribute_id = g_entity_type_attr
AND fea.version_id = g_entity_type_version
AND fea.dim_attribute_varchar_member <> 'E'
AND fea.entity_id = gea.entity_id
AND gea.data_type_code = p_balance_type_code
AND p_end_date BETWEEN gea.effective_start_date
AND NVL(gea.effective_end_date, p_end_date )
AND p_end_date BETWEEN gcr.start_date AND
nvl(gcr.end_date, p_end_date);
SELECT status_code
FROM gcs_data_sub_dtls gdsd,
fem_entities_attr fea,
gcs_cons_relationships gcr,
gcs_cal_period_maps_gt gcpmt,
fem_ledgers_attr fla,
gcs_entity_cons_attrs geca,
gcs_entities_attr gea
WHERE gcr.child_entity_id = gdsd.entity_id
AND p_cal_period_id = gcpmt.target_cal_period_id
AND gdsd.cal_period_id = gcpmt.source_cal_period_id
AND gdsd.balance_type_code = p_balance_type_code
AND gdsd.most_recent_flag = 'Y'
AND NVL(gdsd.currency_code, fla.dim_attribute_varchar_member) =
geca.currency_code
AND gea.entity_id = gdsd.entity_id
AND gea.data_type_code = gdsd.balance_type_code
AND p_end_date BETWEEN gea.effective_start_date
AND NVL(gea.effective_end_date, p_end_date )
AND fla.ledger_id = gea.ledger_id
AND fla.attribute_id = g_ledger_currency_attr
AND fla.version_id = g_ledger_currency_version
AND geca.hierarchy_id = gcr.hierarchy_id
AND geca.entity_id = gdsd.entity_id
AND gcr.child_entity_id = fea.entity_id
AND gcr.hierarchy_id = p_hierarchy_id
AND gcr.parent_entity_id = p_entity_id
AND gcr.dominant_parent_flag = 'Y'
AND fea.attribute_id = g_entity_type_attr
AND fea.version_id = g_entity_type_version
AND fea.dim_attribute_varchar_member = 'O'
AND p_end_date BETWEEN gcr.start_date AND NVL(gcr.end_date, p_end_date);
SELECT gcds.status_code
FROM gcs_cons_data_statuses gcds,
fem_entities_attr fea,
gcs_cons_relationships gcr
WHERE gcr.child_entity_id = gcds.consolidation_entity_id
AND gcds.cal_period_id = p_cal_period_id
AND gcds.balance_type_code = p_balance_type_code
AND gcds.hierarchy_id = p_hierarchy_id
AND gcr.child_entity_id = fea.entity_id
AND gcr.hierarchy_id = p_hierarchy_id
AND gcr.parent_entity_id = p_entity_id
AND gcr.dominant_parent_flag = 'Y'
AND fea.attribute_id = g_entity_type_attr
AND fea.version_id = g_entity_type_version
AND fea.dim_attribute_varchar_member = 'C'
AND p_end_date between gcr.start_date AND
NVL(gcr.end_date, p_end_date);
USING (SELECT l_status_code status_code FROM dual) src
ON (gcds.hierarchy_id = p_hierarchy_id AND
gcds.consolidation_entity_id = p_entity_id AND
gcds.cal_period_id = p_cal_period_id AND
gcds.balance_type_code = p_balance_type_code)
WHEN MATCHED THEN
UPDATE
SET gcds.status_code = src.status_code,
gcds.last_update_date = sysdate,
gcds.last_updated_by = fnd_global.user_id
WHEN NOT MATCHED THEN
INSERT
(gcds.hierarchy_id,
gcds.consolidation_entity_id,
gcds.cal_period_id,
gcds.balance_type_code,
gcds.status_code,
gcds.created_by,
gcds.creation_date,
gcds.last_updated_by,
gcds.last_update_date)
VALUES
(p_hierarchy_id,
p_entity_id,
p_cal_period_id,
p_balance_type_code,
src.status_code,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate);
SELECT fcpa.cal_period_id,
gdsd.balance_type_code
FROM gcs_data_sub_dtls gdsd,
fem_cal_periods_attr fcpa,
fem_ledgers_attr fla,
gcs_entity_cons_attrs geca,
fem_entities_attr fea_cur
WHERE gdsd.entity_id = p_entity_id
AND gdsd.cal_period_id = fcpa.cal_period_id
AND gdsd.most_recent_flag = 'Y'
AND fcpa.attribute_id = g_cal_period_end_date_attr
AND fcpa.version_id = g_cal_period_end_date_version
AND fcpa.date_assign_value >= p_start_date
AND NVL(gdsd.currency_code, fla.dim_attribute_varchar_member) =
geca.currency_code
AND fea_cur.entity_id = gdsd.entity_id
AND fea_cur.attribute_id = g_entity_ledger_attr
AND fea_cur.version_id = g_entity_ledger_version
AND fla.ledger_id = fea_cur.dim_attribute_numeric_member
AND fla.attribute_id = g_ledger_currency_attr
AND fla.version_id = g_ledger_currency_version
AND geca.hierarchy_id = p_hierarchy_id
AND geca.entity_id = gdsd.entity_id;
select target_cal_period_id,
fcpa.date_assign_value
from gcs_cal_period_maps_gt gcpmt,
fem_cal_periods_attr fcpa
where gcpmt.target_cal_period_id = fcpa.cal_period_id
and fcpa.attribute_id = g_cal_period_end_date_attr
and fcpa.version_id = g_cal_period_end_date_version;
UPDATE gcs_cons_data_statuses gcds
SET status_code = 'IN_PROGRESS',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE hierarchy_id = p_hierarchy_id
AND status_code in ('COMPLETED', 'WARNING', 'ERROR')
AND consolidation_entity_id in
(SELECT gcr.parent_entity_id
FROM gcs_cons_relationships gcr
START WITH child_entity_id = p_entity_id
AND hierarchy_id = p_hierarchy_id
AND dominant_parent_flag = 'Y'
CONNECT BY prior parent_entity_id = child_entity_id
AND hierarchy_id = p_hierarchy_id
AND dominant_parent_flag = 'Y')
AND EXISTS
(SELECT 1
FROM fem_cal_periods_attr fcpa
WHERE fcpa.cal_period_id = gcds.cal_period_id
AND fcpa.attribute_id = g_cal_period_end_date_attr
AND fcpa.version_id = g_cal_period_end_date_version
AND fcpa.date_assign_value >= p_start_date);
SELECT run_entity_id, status_code
FROM gcs_cons_eng_runs
WHERE NVL(associated_run_name, run_name) = p_runname
START WITH run_entity_id = p_entity_id
AND NVL(associated_run_name, run_name) = p_runname
CONNECT BY PRIOR run_entity_id = parent_entity_id;
UPDATE gcs_cons_eng_runs
SET locked_flag = decode(p_lock_flag, 'Y', 'N', 'Y')
WHERE NVL(associated_run_name, run_name) = p_runname
AND run_entity_id = entity.run_entity_id;
PROCEDURE update_data_status(p_load_id IN NUMBER DEFAULT NULL,
p_cons_rel_id IN NUMBER DEFAULT NULL,
p_hierarchy_id IN NUMBER DEFAULT NULL,
p_transaction_type IN VARCHAR2 DEFAULT NULL) IS
l_load_id NUMBER := p_load_id;
l_api_name VARCHAR2(80) := 'update_data_status';
SELECT DISTINCT ghb.hierarchy_id,
gdsd.entity_id,
fcpb.cal_period_id,
gdsd.balance_type_code,
fcpa.date_assign_value
FROM gcs_data_sub_dtls gdsd,
fem_cal_periods_attr fcpa,
gcs_cal_period_maps_gt gcpmt,
gcs_hierarchies_b ghb,
fem_cal_periods_b fcpb,
fem_ledgers_attr fla,
gcs_entity_cons_attrs geca,
gcs_entities_attr gea,
fem_cal_periods_attr fcpa_curr
WHERE gdsd.cal_period_id = gcpmt.source_cal_period_id
AND fcpb.cal_period_id = fcpa.cal_period_id
AND fcpb.cal_period_id = gcpmt.target_cal_period_id
AND fcpb.calendar_id = ghb.calendar_id
AND fcpb.dimension_group_id = ghb.dimension_group_id
AND fcpa.attribute_id = g_cal_period_end_date_attr
AND fcpa.version_id = g_cal_period_end_date_version
AND gdsd.load_id = p_load_id
AND NVL(gdsd.currency_code, fla.dim_attribute_varchar_member) =
geca.currency_code
AND gea.entity_id = gdsd.entity_id
AND gea.data_type_code = gdsd.balance_type_code
AND fcpa_curr.cal_period_id = gdsd.cal_period_id
AND fcpa_curr.attribute_id = g_cal_period_end_date_attr
AND fcpa_curr.version_id = g_cal_period_end_date_version
AND fcpa_curr.date_assign_value BETWEEN gea.effective_start_date
AND NVL(gea.effective_end_date, fcpa_curr.date_assign_value )
AND gea.ledger_id = fla.ledger_id
AND fla.attribute_id = g_ledger_currency_attr
AND fla.version_id = g_ledger_currency_version
AND geca.hierarchy_id = ghb.hierarchy_id
AND geca.entity_id = gdsd.entity_id;
SELECT gcr.child_entity_id,
gcr.hierarchy_id,
gcr.start_date,
gcr.end_date
FROM gcs_cons_relationships gcr
WHERE cons_relationship_id = p_cons_rel_id;
SELECT gcr.child_entity_id,
gcr.start_date
FROM gcs_cons_relationships gcr,
fem_entities_attr fea
WHERE gcr.hierarchy_id = p_hierarchy_id
AND gcr.dominant_parent_flag = 'Y'
AND gcr.child_entity_id = fea.entity_id
AND fea.attribute_id = g_entity_type_attr
AND fea.version_id = g_entity_type_version
AND fea.dim_attribute_varchar_member = 'O';
SELECT gcds.cal_period_id,
gcds.balance_type_code,
fcpa.date_assign_value
FROM gcs_cons_data_statuses gcds,
fem_cal_periods_attr fcpa
WHERE hierarchy_id = p_hierarchy_id
AND consolidation_entity_id = p_entity_id
AND gcds.cal_period_id = fcpa.cal_period_id
AND fcpa.attribute_id = g_cal_period_end_date_attr
AND fcpa.version_id = g_cal_period_end_date_version
AND fcpa.date_assign_value > p_end_date;
SELECT cal_period_id
INTO l_cal_period_id
FROM gcs_data_sub_dtls
WHERE load_id = l_load_id;
END update_data_status;
SELECT gcr.child_entity_id,
gcr.start_date
FROM gcs_cons_relationships gcr,
fem_entities_attr fea
WHERE gcr.hierarchy_id = p_hierarchy_id
AND gcr.dominant_parent_flag = 'Y'
AND gcr.child_entity_id = fea.entity_id
AND fea.attribute_id = g_entity_type_attr
AND fea.version_id = g_entity_type_version
AND fea.dim_attribute_varchar_member = 'O';
PROCEDURE submit_update_data_status(x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_load_id IN NUMBER DEFAULT NULL,
p_cons_rel_id IN NUMBER DEFAULT NULL,
p_hierarchy_id IN NUMBER DEFAULT NULL,
p_transaction_type IN VARCHAR2 DEFAULT NULL) IS
l_api_name VARCHAR2(80) := 'submit_update_data_status';
update_data_status(p_load_id => p_load_id,
p_cons_rel_id => p_cons_rel_id,
p_hierarchy_id => p_hierarchy_id,
p_transaction_type => p_transaction_type);
END submit_update_data_status;