[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(value,1)*2 no_of_workers
INTO v_Num_Workers
FROM v$parameter
WHERE NAME = 'cpu_count';
SELECT run_name
INTO p_key
FROM gcs_cons_eng_runs gcer
WHERE gcer.hierarchy_id = p_consolidation_hierarchy
AND gcer.cal_period_id = p_cal_period_id
AND gcer.balance_type_code = p_balance_type_code
AND gcer.most_recent_flag = 'Y'
AND gcer.run_entity_id = p_consolidation_entity;
ad_parallel_updates_pkg.purge_processed_units(X_owner => 'GCS',
X_table => 'GCS_CONS_ENG_RUNS',
X_script => p_key);
ad_parallel_updates_pkg.delete_update_information(X_update_type => ad_parallel_updates_pkg.ROWID_RANGE,
X_owner => 'GCS',
X_table => 'GCS_CONS_ENG_RUNS',
X_script => p_key);
SELECT DISTINCT run_name, run_entity_id, entity_name
FROM gcs_cons_eng_runs gcer,
fem_entities_tl fem
WHERE gcer.run_entity_id = fem.entity_id
AND gcer.hierarchy_id = p_consolidation_hierarchy
AND gcer.cal_period_id = p_cal_period_id
AND gcer.balance_type_code = p_balance_type_code
AND gcer.most_recent_flag <> 'Y'
AND gcer.status_code <> 'PURGED'
AND (gcer.associated_run_name IS NULL
OR (gcer.associated_run_name IS NOT NULL
AND NOT EXISTS (
SELECT run_name
FROM gcs_cons_eng_runs gcer_inner
WHERE gcer_inner.hierarchy_id=p_consolidation_hierarchy
AND gcer_inner.cal_period_id = p_cal_period_id
AND gcer_inner.balance_type_code = p_balance_type_code
AND gcer_inner.most_recent_flag = 'Y'
AND gcer_inner.run_name=gcer.associated_run_name
AND gcer_inner.run_entity_id=gcer.run_entity_id
)
)
)
AND fem.language = userenv('lang')
AND gcer.ROWID BETWEEN p_start_rowid AND p_end_rowid
START WITH gcer.run_entity_id = p_consolidation_entity
AND gcer.hierarchy_id = p_consolidation_hierarchy
AND gcer.cal_period_id = p_cal_period_id
AND gcer.balance_type_code = p_balance_type_code
CONNECT BY PRIOR gcer.run_entity_id = gcer.parent_entity_id
AND gcer.hierarchy_id = p_consolidation_hierarchy
AND gcer.cal_period_id = p_cal_period_id
AND gcer.balance_type_code = p_balance_type_code;
SELECT DISTINCT gcerd_outer.entry_id,gcerd_outer.child_entity_id,geh.entry_name,geh.description
FROM gcs_cons_eng_runs gcer_outer,
gcs_cons_eng_run_dtls gcerd_outer,
gcs_entry_headers geh
WHERE gcer_outer.run_name = gcerd_outer.run_name
AND gcer_outer.run_entity_id = gcerd_outer.consolidation_entity_id
AND gcerd_outer.entry_id = geh.entry_id
AND geh.entry_type_code = 'AUTOMATIC'
AND gcerd_outer.run_name = p_run_name
AND gcerd_outer.consolidation_entity_id = p_run_entity_id
AND gcerd_outer.entry_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM gcs_cons_eng_runs gcer_inner,
gcs_cons_eng_run_dtls gcerd_inner
WHERE gcer_inner.most_recent_flag = 'Y'
AND gcer_inner.hierarchy_id = gcer_outer.hierarchy_id
AND gcer_inner.run_entity_id = gcer_outer.run_entity_id
AND gcer_inner.cal_period_id = gcer_outer.cal_period_id
AND gcer_inner.balance_type_code = gcer_outer.balance_type_code
AND gcerd_inner.entry_id = gcerd_outer.entry_id
AND gcer_inner.run_name = gcerd_inner.run_name
)
UNION ALL
SELECT DISTINCT gcerd_outer.stat_entry_id,gcerd_outer.child_entity_id,geh.entry_name,geh.description
FROM gcs_cons_eng_runs gcer_outer,
gcs_cons_eng_run_dtls gcerd_outer,
gcs_entry_headers geh
WHERE gcer_outer.run_name = gcerd_outer.run_name
AND gcer_outer.run_entity_id = gcerd_outer.consolidation_entity_id
AND gcerd_outer.stat_entry_id = geh.entry_id
AND geh.entry_type_code = 'AUTOMATIC'
AND gcerd_outer.run_name = p_run_name
AND gcerd_outer.consolidation_entity_id = p_run_entity_id
AND gcerd_outer.stat_entry_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM gcs_cons_eng_runs gcer_inner,
gcs_cons_eng_run_dtls gcerd_inner
WHERE gcer_inner.most_recent_flag = 'Y'
AND gcer_inner.hierarchy_id = gcer_outer.hierarchy_id
AND gcer_inner.run_entity_id = gcer_outer.run_entity_id
AND gcer_inner.cal_period_id = gcer_outer.cal_period_id
AND gcer_inner.balance_type_code = gcer_outer.balance_type_code
AND gcerd_inner.stat_entry_id = gcerd_outer.stat_entry_id
AND gcer_inner.run_name = gcerd_inner.run_name
);
SELECT run_name
INTO p_key
FROM gcs_cons_eng_runs gcer
WHERE gcer.hierarchy_id = p_consolidation_hierarchy
AND gcer.cal_period_id = p_cal_period_id
AND gcer.balance_type_code = p_balance_type_code
AND gcer.most_recent_flag = 'Y'
AND gcer.run_entity_id = p_consolidation_entity;
ad_parallel_updates_pkg.initialize_rowid_range( ad_parallel_updates_pkg.ROWID_RANGE,
v_table_owner,
v_table_name,
p_key,
p_Worker_Id,
p_Num_Workers,
p_batch_size,
0);
ad_parallel_updates_pkg.get_rowid_range( v_start_rowid,
v_end_rowid,
v_any_rows_to_process,
p_batch_size,
TRUE);
DELETE FROM gcs_entry_lines WHERE entry_id = l_purge_entry_id_rec(j).entry_id;
DELETE FROM gcs_entry_headers WHERE entry_id = l_purge_entry_id_rec(j).entry_id;
SELECT entity_name
INTO l_entity_name
FROM fem_entities_tl fem
WHERE fem.entity_id = l_purge_entry_id_rec(j).child_entity_id
AND fem.language = userenv('lang');
DELETE FROM gcs_cons_eng_run_dtls gcerd
WHERE gcerd.run_name = l_purge_cons_entity_list(i).run_name
AND gcerd.consolidation_entity_id = l_purge_cons_entity_list(i).run_entity_id;
UPDATE gcs_cons_eng_runs
SET status_code = 'PURGED'
WHERE run_name = l_purge_cons_entity_list(i).run_name
AND run_entity_id = l_purge_cons_entity_list(i).run_entity_id;
ad_parallel_updates_pkg.processed_rowid_range( v_rows_processed, v_end_rowid);
ad_parallel_updates_pkg.get_rowid_range( v_start_rowid,
v_end_rowid,
v_any_rows_to_process,
p_batch_size,
FALSE);