[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_null_return varchar2(1); -- for 'select null' statements
CURSOR stu -- selects all rows from startup entity
IS
select context_name c_true_key
, rowid
, context_level
, data_type
, context_id c_surrogate_key
from hr_s_contexts;
stu_rec stu%ROWTYPE; -- Record for above SELECT
hr_legislation.insert_hr_stu_exceptions('ff_contexts'
, stu_rec.c_surrogate_key
, exception_type
, stu_rec.c_true_key);
-- 1. Where the newly select sequence value conflicts with values
-- in the STU tables.
-- 2. Where selected surrogate keys, from the installed tables,
-- conflict with other rows in the STU tables.
--
-- Both of the above scenario's are tested for.
-- The first is a simple match, where if a value is detected in the
-- STU tables and the installed tables then a conflict is detected. In
-- This instance all STU surrogate keys, for this table, are updated.
-- The second is tested for using the sequences.
-- If the next value from the live sequence is within the range of
-- delivered surrogate id's then the live sequence must be incremented.
-- If no action is taken, then duplicates may be introduced into the
-- delivered tables, and child rows may be totally invalidated.
BEGIN
BEGIN --check that the installde routes will not conflict
--with the delivered values
select distinct null
into l_null_return
from ff_contexts a
where exists
(select null
from hr_s_contexts b
where a.context_id = b.context_id
);
--update all context_id's to remove conflict
update /*+NO_INDEX*/ hr_s_contexts
set context_id = context_id - 50000000;
update /*+NO_INDEX*/ hr_s_route_context_usages
set context_id = context_id - 50000000;
update /*+NO_INDEX*/ hr_s_ftype_context_usages
set context_id = context_id - 50000000;
update /*+NO_INDEX*/ hr_s_function_context_usages
set context_id = context_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'CONTEXT_ID';
select min(context_id) - (count(*) *3)
, max(context_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_contexts;
select ff_contexts_s.nextval
into v_sequence_number
from dual;
PROCEDURE update_uid
--------------------
IS
-- Subprogram to update surrogate UID and all occurrences in child rows
r_count NUMBER;
select distinct context_id
into l_new_surrogate_key
from ff_contexts
where context_name = stu_rec.c_true_key;
select ff_contexts_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_contexts
set context_id = l_new_surrogate_key
where context_id = stu_rec.c_surrogate_key;
update hr_s_application_ownerships
set key_value = to_char(l_new_surrogate_key)
where key_value = to_char(stu_rec.c_surrogate_key)
and key_name = 'CONTEXT_ID';
update hr_s_ftype_context_usages
set context_id = l_new_surrogate_key
where context_id = stu_rec.c_surrogate_key;
update hr_s_route_context_usages
set context_id = l_new_surrogate_key
where context_id = stu_rec.c_surrogate_key;
update hr_s_function_context_usages
set context_id = l_new_surrogate_key
where context_id = stu_rec.c_surrogate_key;
END update_uid;
delete from hr_s_contexts
where rowid = stu_rec.rowid;
delete from hr_stu_exceptions
where surrogate_id = stu_rec.c_surrogate_key
and table_name = 'FF_CONTEXTS';
-- then this row is not required and may be deleted from the delivery
-- tables.
-- If legislation code and subgroup code are included on the delivery
-- tables, a check must be made to determine if the data is defined for
-- a specific subgroup. If so the subgroup must be 'A'ctive for this
-- installation.
-- A return code of TRUE indicates that the row is required.
-- The exception is raised within this procedure if no rows are returned
-- in this select statement. If no rows are returned then one of the
-- following is true:
-- 1. No ownership parameters are defined.
-- 2. The products, for which owning parameters are defined, are not
-- installed with as status of 'I'.
-- 3. The data is defined for a legislation subgroup that is not active.
IF p_phase <> 1 THEN --only perform in phase 1
return TRUE;
select null --if exception raised then this row is not needed
into l_null_return
from dual
where exists (
select null
from hr_s_application_ownerships a
, fnd_product_installations b
, fnd_application c
where a.key_name = 'CONTEXT_ID'
and a.key_value = l_new_surrogate_key
and a.product_name = c.application_short_name
and c.application_id = b.application_id
and ((b.status = 'I' and c.application_short_name <> 'PQP')
or
(b.status in ('I', 'S') and c.application_short_name = 'PQP')));
-- Check if a delivered row is needed and insert into the
-- live tables if it is.
BEGIN
-- A simplistic installation. If the context name already exists
-- in the live tables then no exception is raised and the row is
-- delete from the delivery tables. If the context name does not
-- exist then an exception is raised. The actual insert only occurs
-- in phase 2 and after the insert has been performed the delivered
-- row must be deleted.
select null
into l_null_return
from ff_contexts
where context_name = stu_rec.c_true_key;
-- Row not required so delete
remove;
-- No inserts in phase 1
IF p_phase = 1 THEN
return;
insert into ff_contexts
(context_id
,context_level
,context_name
,data_type
)
values
(stu_rec.c_surrogate_key
,stu_rec.context_level
,stu_rec.c_true_key
,stu_rec.data_type);
-- Delete delivered row now it has been installed
remove;
IF p_phase = 1 THEN update_uid; END IF;
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select formula_type_name c_true_key
, rowid
, formula_type_id c_surrogate_key
, type_description
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
from hr_s_formula_types;
select *
from hr_s_ftype_context_usages
where formula_type_id = p_ftype_id;
stu_rec stu%ROWTYPE; -- Record definition for cursor select
hr_legislation.insert_hr_stu_exceptions('ff_formula_types'
, stu_rec.c_surrogate_key
, exception_type
, stu_rec.c_true_key);
select distinct null
from ff_formula_types a
where exists
(select null
from hr_s_formula_types b
where a.formula_type_id = b.formula_type_id
);
-- 1. Where the newly select sequence value conflicts with values
-- in the STU tables.
-- 2. Where selected surrogate keys, from the installed tables,
-- conflict with other rows in the STU tables.
--
-- Both of the above scenario's are tested for.
-- The first is a simple match, where if a value is detected in the
-- STU tables and the installed tables then a conflict is detected. In
-- This instance all STU surrogate keys, for this table, are updated.
-- The second is tested for using the sequences.
-- If the next value from the live sequence is within the range of
-- delivered surrogate id's then the live sequence must be incremented.
-- If no action is taken, then duplicates may be introduced into the
-- delivered tables, and child rows may be totally invalidated.
-- This procedure will check three sequences
BEGIN
BEGIN --check that the installde routes will not conflict
--with the delivered values
--
open c_fft1;
--update all formula_type_id's to remove conflict
update hr_s_formula_types
set formula_type_id = formula_type_id - 50000000;
update hr_s_formulas_f
set formula_type_id = formula_type_id - 50000000;
update hr_s_ftype_context_usages
set formula_type_id = formula_type_id - 50000000;
update hr_s_qp_reports
set formula_type_id = formula_type_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'FORMULA_TYPE_ID';
select min(formula_type_id) - (count(*) *3)
, max(formula_type_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_formula_types;
select ff_formula_types_s.nextval
into v_sequence_number
from dual;
PROCEDURE update_uid
--------------------
IS
-- Subprogram to update surrogate UID and all occurrences in child rows
BEGIN
BEGIN
select distinct formula_type_id
into l_new_surrogate_key
from ff_formula_types
where formula_type_name = stu_rec.c_true_key;
select ff_formula_types_s.nextval
into l_new_surrogate_key
from dual;
update hr_s_formula_types
set formula_type_id = l_new_surrogate_key
where formula_type_id = stu_rec.c_surrogate_key;
update hr_s_application_ownerships
set key_value = to_char(l_new_surrogate_key)
where key_value = to_char(stu_rec.c_surrogate_key)
and key_name = 'FORMULA_TYPE_ID';
update hr_s_formulas_f
set formula_type_id = l_new_surrogate_key
where formula_type_id = stu_rec.c_surrogate_key;
update hr_s_ftype_context_usages
set formula_type_id = l_new_surrogate_key
where formula_type_id = stu_rec.c_surrogate_key;
update hr_s_qp_reports
set formula_type_id = l_new_surrogate_key
where formula_type_id = stu_rec.c_surrogate_key;
END update_uid;
delete from hr_s_formula_types
where rowid = stu_rec.rowid;
delete from hr_s_ftype_context_usages
where formula_type_id = l_new_surrogate_key;
select null --if exception raised then this row is not needed
from dual
where exists (
select null
from hr_s_application_ownerships a
, fnd_product_installations b
, fnd_application c
where a.key_name = 'FORMULA_TYPE_ID'
and a.key_value = l_new_surrogate_key
and a.product_name = c.application_short_name
and c.application_id = b.application_id
and ((b.status = 'I' and c.application_short_name <> 'PQP')
or
(b.status in ('I', 'S') and c.application_short_name = 'PQP')));
select 1
from dual
where exists
(select hfcu.context_id
from hr_s_ftype_context_usages hfcu
where hfcu.formula_type_id = l_new_surrogate_key
MINUS
select fcu.context_id
from ff_ftype_context_usages fcu
where fcu.formula_type_id = l_new_surrogate_key);
-- then this row is not required and may be deleted from the delivery
-- tables.
-- If legislation code and subgroup code are included on the delivery
-- tables, a check must be made to determine if the data is defined for
-- a specific subgroup. If so the subgroup must be 'A'ctive for this
-- installation.
-- A return code of TRUE indicates that the row is required.
-- The exception is raised within this procedure if no rows are returned
-- in this select statement. If no rows are returned then one of the
-- following is true:
-- 1. No ownership parameters are defined.
-- 2. The products, for which owning parameters are defined, are not
-- installed with as status of 'I'.
-- 3. The data is defined for a legislation subgroup that is not active.
IF p_phase <> 1 THEN -- Only perform in phase 1
return TRUE;
select count(*)
into l_number_of_ftcu
from hr_s_ftype_context_usages
where formula_type_id = l_new_surrogate_key;
select null
from ff_contexts
where context_id = usages_context_id;
update ff_formula_types
set type_description = stu_rec.type_description
, last_update_date = stu_rec.last_update_date
, last_updated_by = stu_rec.last_updated_by
, last_update_login = stu_rec.last_update_login
, created_by = stu_rec.created_by
, creation_date = stu_rec.creation_date
where formula_type_name = stu_rec.c_true_key;
insert into ff_formula_types
(formula_type_name
,formula_type_id
,type_description
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(stu_rec.c_true_key
,stu_rec.c_surrogate_key
,stu_rec.type_description
,stu_rec.last_update_date
,stu_rec.last_updated_by
,stu_rec.last_update_login
,stu_rec.created_by
,stu_rec.creation_date
);
-- proceed. The actual insert statement is within a phase value check
-- to allow any errors with parent contexts to be highlighted in pahse 1.
-- the last final delete will only occur in phase 2, since only in phase 2
-- will the row have been transferred.
-- All the live context usages must be deleted first
FOR usages IN ftcu(l_new_surrogate_key) LOOP
BEGIN
delete ff_compiled_info_f f
where f.formula_id in (
select distinct a.formula_id
from ff_formulas_f a,
ff_fdi_usages_f b,
ff_contexts c
where a.formula_type_id = stu_rec.c_surrogate_key
and a.formula_id = b.formula_id
and b.item_name = upper(c.context_name)
and c.context_id = usages.context_id
and b.usage = 'U');
delete ff_fdi_usages_f f
where f.formula_id in (
select distinct a.formula_id
from ff_formulas_f a,
ff_fdi_usages_f b,
ff_contexts c
where a.formula_type_id = stu_rec.c_surrogate_key
and a.formula_id = b.formula_id
and b.item_name = upper(c.context_name)
and c.context_id = usages.context_id
and b.usage = 'U');
delete from ff_ftype_context_usages
where formula_type_id = stu_rec.c_surrogate_key
and context_id=usages.context_id;
insert into ff_ftype_context_usages
(formula_type_id
,context_id)
values
(usages.formula_type_id
,usages.context_id);
-- Delete delivered row now it has been installed
remove;
update_uid;
-- select statement used for the main loop. Each row return is used
-- as the commit unit, since each true primary key may have many date
-- effective rows for it.
-- The selected primary key is then passed into the second driving
-- cursor statement as a parameter, and all date effective rows for
-- this primary key are then selected.
select max(effective_end_date) c_end
, formula_id c_surrogate_key
, formula_type_id
, formula_name c_true_key
, legislation_code
from hr_s_formulas_f
group by formula_id
, formula_type_id
, formula_name
, legislation_code;
-- Selects all date effective rows for the current true primary key
-- The primary key has already been selected using the above cursor.
-- This cursor accepts the primary key as a parameter and selects all
-- date effective rows for it.
select *
from hr_s_formulas_f
where formula_id = pc_formula_id;
hr_legislation.insert_hr_stu_exceptions('ff_formulas_f'
, r_distinct.c_surrogate_key
, exception_type
, r_distinct.c_true_key);
-- Subprogram to delete a row from the delivery tables, and all child
-- application ownership rows
BEGIN
delete from hr_s_formulas_f
where formula_id = v_id;
-- 1. Where the newly select sequence value conflicts with values
-- in the STU tables.
-- 2. Where selected surrogate keys, from the installed tables,
-- conflict with other rows in the STU tables.
--
-- Both of the above scenario's are tested for.
-- The first is a simple match, where if a value is detected in the
-- STU tables and the installed tables then a conflict is detected. In
-- This instance all STU surrogate keys, for this table, are updated.
-- The second is tested for using the sequences.
-- If the next value from the live sequence is within the range of
-- delivered surrogate id's then the live sequence must be incremented.
-- If no action is taken, then duplicates may be introduced into the
-- delivered tables, and child rows may be totally invalidated.
BEGIN
BEGIN --check that the installed id's will not conflict
--with the delivered values
select distinct null
into l_null_return
from ff_formulas_f a
where exists
(select null
from hr_s_formulas_f b
where a.formula_id = b.formula_id
);
--update all formula_id's to remove conflict
update /*+NO_INDEX*/ hr_s_formulas_f
set formula_id = formula_id - 50000000;
update /*+NO_INDEX*/ hr_s_qp_reports
set formula_id = formula_id - 50000000;
update /*+NO_INDEX*/ hr_s_element_types_f
set formula_id = formula_id - 50000000;
update /*+NO_INDEX*/ hr_s_element_types_f
set iterative_formula_id = iterative_formula_id - 50000000;
update /*+NO_INDEX*/ hr_s_element_types_f
set proration_formula_id = proration_formula_id - 50000000;
update /*+NO_INDEX*/ hr_s_input_values_f
set formula_id = formula_id - 50000000;
update /*+NO_INDEX*/ hr_s_status_processing_rules_f
set formula_id = formula_id - 50000000;
update /*+NO_INDEX*/ hr_s_user_columns
set formula_id = formula_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'FORMULA_ID';
update /*+NO_INDEX*/ hr_s_magnetic_records
set formula_id = formula_id - 50000000;
update hr_s_legislation_rules
set rule_mode =
to_char(fnd_number.canonical_to_number(rule_mode) - 50000000)
where rule_type = 'LEGISLATION_CHECK_FORMULA';
select min(formula_id) - (count(*) *3)
, max(formula_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_formulas_f;
select ff_formulas_s.nextval
into v_sequence_number
from dual;
PROCEDURE update_uid
--------------------
IS
-- Subprogram to update surrogate UID and all occurrences in child rows
-- See if this primary key is already installed. If so then the sorrogate
-- key of the delivered row must be updated to the value in the installed
-- tables. If the row is not already present then select the next value
-- from the sequence. In either case all rows for this primary key must
-- be updated, as must all child references to the old surrogate uid.
BEGIN
BEGIN
select distinct formula_id
into l_new_formula_id
from ff_formulas_f
where formula_name = r_distinct.c_true_key
and formula_type_id = r_distinct.formula_type_id
and business_Group_id is null
and ((legislation_code is NULL and r_distinct.legislation_code is NULL)
or (r_distinct.legislation_code=legislation_code));
select ff_formulas_s.nextval
into l_new_formula_id
from dual;
update hr_s_formulas_f
set formula_id = l_new_formula_id
where formula_id = r_distinct.c_surrogate_key;
update hr_s_application_ownerships
set key_value = to_char(l_new_formula_id)
where key_value = to_char(r_distinct.c_surrogate_key)
and key_name = 'FORMULA_ID';
update hr_s_qp_reports
set formula_id = l_new_formula_id
where formula_id = r_distinct.c_surrogate_key;
update hr_s_element_Types_f
set formula_id = l_new_formula_id
where formula_id = r_distinct.c_surrogate_key;
update hr_s_element_Types_f
set iterative_formula_id = l_new_formula_id
where iterative_formula_id = r_distinct.c_surrogate_key;
update hr_s_element_Types_f
set proration_formula_id = l_new_formula_id
where proration_formula_id = r_distinct.c_surrogate_key;
update hr_s_input_values_f
set formula_id = l_new_formula_id
where formula_id = r_distinct.c_surrogate_key;
update hr_s_status_processing_rules_f
set formula_id = l_new_formula_id
where formula_id = r_distinct.c_surrogate_key;
update hr_s_user_columns
set formula_id = l_new_formula_id
where formula_id = r_distinct.c_surrogate_key;
update hr_s_magnetic_records
set formula_id = l_new_formula_id
where formula_id = r_distinct.c_surrogate_key;
update hr_s_legislation_rules
set rule_mode = to_char(l_new_formula_id)
where rule_mode = to_char(r_distinct.c_surrogate_key)
and rule_type = 'LEGISLATION_CHECK_FORMULA';
END update_uid;
select distinct null
into l_null_return
from pay_input_values_f
where effective_end_date > r_distinct.c_end
and formula_id = l_new_formula_id
and business_group_id is not null;
select distinct null
into l_null_return
from pay_status_processing_rules_f
where effective_end_date > r_distinct.c_end
and formula_id = l_new_formula_id
and business_group_id is not null;
-- every foriegn key. The first select from the delivery tables.
-- If a row is founnd then the installation of the parent must have
-- failed, and this installation must not go ahead. If no data is
-- found, ie: an exception is raised, the installation is valid.
-- The second check looks for a row in the live tables. If no rows
-- are returned then this installation is invalid, since this means
-- that the parent referenced by this row is not present in the
-- live tables.
-- Return code of true indicates that all parental data is correct.
BEGIN
-- Start first parent check
BEGIN
-- Check first parent does not exist in the delivery tables
select distinct null
into l_NULL_RETURN
from hr_s_formula_types
where formula_type_id = r_each_row.formula_type_id;
select null
into l_null_return
from ff_formula_types
where formula_type_id = r_each_row.formula_type_id;
select distinct null
into l_null_return
from ff_formulas_f a
where a.formula_name = r_distinct.c_true_key
and a.formula_type_id = r_distinct.formula_type_id
and a.business_group_id is not null
and exists (select null from per_business_groups b
where b.business_group_id = a.business_group_id
and b.legislation_code = nvl(r_distinct.legislation_code,b.legislation_code));
select distinct null
into l_null_return
from ff_formulas_f
where formula_name = r_distinct.c_true_key
and formula_type_id = r_distinct.formula_type_id
and legislation_code <> r_distinct.legislation_code
and (legislation_code is null or
r_distinct.legislation_code is null );
-- tables the row is either deleted or not. If the delivered row
-- is 'stamped' with a legislation subgroup, then a check must be
-- made to see if that subgroup is active or not. This check only
-- needs to be performed in phase 1, since once this decision is
-- made, it is pontless to perform this logic again.
-- The exception is raised within this procedure if no rows are returned
-- in this select statement. If no rows are returned then one of the
-- following is true:
-- 1. No ownership parameters are defined.
-- 2. The products, for which owning parameters are defined, are not
-- installed with as status of 'I'.
-- 3. The data is defined for a legislation subgroup that is not active.
IF p_phase <> 1 THEN
return TRUE;
select null --if exception raised then this row is not needed
into l_null_return
from dual
where exists (select null
from hr_s_application_ownerships a
, fnd_product_installations b
, fnd_application c
where a.key_name = 'FORMULA_ID'
and a.key_value = r_distinct.c_surrogate_key
and a.product_name = c.application_short_name
and c.application_id = b.application_id
and ((b.status = 'I' and c.application_short_name <> 'PQP')
or
(b.status in ('I', 'S') and c.application_short_name = 'PQP')));
select effective_start_date
from hr_s_formulas_f
where formula_id = f_id;
select null
into l_dummy
from ff_formulas_f
where formula_id = form_id;
select formula_text
into v_formula_text
from hr_s_formulas_f
where formula_id = form_id
and effective_start_date = r_all_rows.effective_start_date;
select formula_text
into vlive_formula_text
from ff_formulas_f
where formula_id = form_id
and effective_start_date = r_all_rows.effective_start_date;
select null
into l_dummy
from dual
where not exists
((
select effective_start_date,
effective_end_date,
description
from hr_s_formulas_f
where formula_id = form_id
and formula_type_id = r_distinct.formula_type_id
MINUS
select effective_start_date,
effective_end_date,
description
from ff_formulas_f
where formula_id = form_id
and formula_type_id = r_distinct.formula_type_id
)
UNION
(
select effective_start_date,
effective_end_date,
description
from ff_formulas_f
where formula_id = form_id
and formula_type_id = r_distinct.formula_type_id
MINUS
select effective_start_date,
effective_end_date,
description
from hr_s_formulas_f
where formula_id = form_id
and formula_type_id = r_distinct.formula_type_id
));
-- Get new surrogate id and update child references
update_uid;
delete from ff_fdi_usages_f
where formula_id = r_distinct.c_surrogate_key;
delete from ff_compiled_info_f
where formula_id = r_distinct.c_surrogate_key;
delete from ff_formulas_f
where formula_id = r_distinct.c_surrogate_key;
insert into ff_formulas_f
(formula_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,formula_type_id
,formula_name
,description
,formula_text
,sticky_flag
,compile_flag
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(r_each_row.formula_id
,r_each_row.effective_start_date
,r_each_row.effective_end_date
,r_each_row.business_group_id
,r_each_row.legislation_code
,r_each_row.formula_type_id
,r_each_row.formula_name
,r_each_row.description
,r_each_row.formula_text
,r_each_row.sticky_flag
,r_each_row.compile_flag
,r_each_row.last_update_date
,r_each_row.last_updated_by
,r_each_row.last_update_login
,r_each_row.created_by
,r_each_row.creation_date
);
l_null_return varchar2(1); -- For 'select null' statements
select route_id route_id,
route_name route_name
from hr_s_routes
where last_update_login = 159260;
CURSOR stu -- Selects all rows from startup entity
IS
select route_id c_surrogate_key
,route_name c_true_key
,user_defined_flag
,description
,text
,nvl(last_update_date,to_date('01-01-0001','DD-MM-YYYY')) last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,optimizer_hint
,rowid
from hr_s_routes;
select *
from hr_s_user_entities
where route_id = r_id;
select distinct *
from hr_s_route_context_usages
where route_id = r_id;
select ROUTE_PARAMETER_ID
, ROUTE_ID
, DATA_TYPE
, PARAMETER_NAME
, SEQUENCE_NO
, rowid
from hr_s_route_parameters
where route_id = r_id;
select *
from hr_s_route_parameter_values
where user_entity_id = ue_id;
select *
from hr_s_database_items
where user_entity_id = ue_id;
hr_legislation.insert_hr_stu_exceptions('ff_routes'
, stu_rec.c_surrogate_key
, exception_type
, stu_rec.c_true_key);
-- 1. Where the newly select sequence value conflicts with values
-- in the STU tables.
-- 2. Where selected surrogate keys, from the installed tables,
-- conflict with other rows in the STU tables.
--
-- Both of the above scenario's are tested for.
-- The first is a simple match, where if a value is detected in the
-- STU tables and the installed tables then a conflict is detected. In
-- This instance all STU surrogate keys, for this table, are updated.
-- Three tables are tested:
-- 1. ff_routes
-- 2. ff_user_entities
-- 3. ff_route_parameters
-- The second is tested for using the sequences.
-- If the next value from the live sequence is within the range of
-- delivered surrogate id's then the live sequence must be incremented.
-- If no action is taken, then duplicates may be introduced into the
-- delivered tables, and child rows may be totally invalidated.
-- This procedure will check three sequences
-- 1. ff_routes_S
-- 2. ff_user_entities_s
-- 3. ff_route_parameters_s
v_sequence_number number(9);
select distinct null
from ff_routes a
where exists
(select null
from hr_s_routes b
where a.route_id = b.route_id
);
select distinct null
from ff_route_parameters a
where exists
(select null
from hr_s_route_parameters b
where b.route_parameter_id = a.route_parameter_id
);
select distinct null
from ff_user_entities a
where exists
(select null
from hr_s_user_entities b
where a.user_entity_id = b.user_entity_id
);
--update all route_id's to remove conflict
update /*+NO_INDEX*/ hr_s_routes
set route_id = route_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'ROUTE_ID';
update /*+NO_INDEX*/ hr_s_balance_dimensions
set route_id = route_id - 50000000;
update /*+NO_INDEX*/ hr_s_dimension_routes
set route_id = route_id - 50000000;
update /*+NO_INDEX*/ hr_s_route_context_usages
set route_id = route_id - 50000000;
update /*+NO_INDEX*/ hr_s_user_entities
set route_id = route_id - 50000000;
update /*+NO_INDEX*/ hr_s_route_parameters
set route_id = route_id - 50000000;
--Conflict exists, so update the stu values of the parameter id
update hr_s_route_parameters
set route_parameter_id = route_parameter_id -50000000;
update hr_s_route_parameter_values
set route_parameter_id = route_parameter_id -50000000;
--conflict exists, so update the stu values of user_entity_id
update /*+NO_INDEX*/ hr_s_user_entities
set user_entity_id = user_entity_id -50000000;
update /*+NO_INDEX*/ hr_s_database_items
set user_entity_id = user_entity_id -50000000;
update /*+NO_INDEX*/ hr_s_route_parameter_values
set user_entity_id = user_entity_id -50000000;
update /*+NO_INDEX*/ hr_s_report_format_items_f
set user_entity_id = user_entity_id -50000000;
select min(route_id) - (count(*) *3)
, max(route_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_routes;
select ff_routes_s.nextval
into v_sequence_number
from dual;
select min(user_entity_id) - (count(*) *3)
, max(user_entity_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_user_entities;
select ff_user_entities_s.nextval
into v_sequence_number
from dual;
select min(route_parameter_id) - (count(*) *3)
, max(route_parameter_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_route_parameters;
select ff_route_parameters_s.nextval
into v_sequence_number
from dual;
PROCEDURE update_uid
--------------------
IS
-- Subprogram to update surrogate UID and all occurrences in child rows
l_new_parameter_id number(9);
select distinct ffu.formula_id fid
from ff_fdi_usages_f ffu
where ffu.item_name in (select fdbi.user_name
from ff_database_items fdbi,
ff_user_entities fue
where fdbi.user_entity_id = fue.user_entity_id
and fue.route_id = p_route_id);
select distinct route_id
into l_new_surrogate_key
from ff_routes
where route_name = stu_rec.c_true_key
and user_defined_flag = 'N';
select ff_routes_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_routes
set route_id = l_new_surrogate_key
where rowid = stu_rec.rowid;
update hr_s_application_ownerships
set key_value = to_char(l_new_surrogate_key)
where key_value = to_char(stu_rec.c_surrogate_key)
and key_name = 'ROUTE_ID';
update hr_s_balance_dimensions
set route_id = l_new_surrogate_key
where route_id = stu_rec.c_Surrogate_key;
update hr_s_dimension_routes
set route_id = l_new_surrogate_key
where route_id = stu_rec.c_Surrogate_key;
update hr_s_route_context_usages
set route_id = l_new_surrogate_key
where route_id = stu_rec.c_Surrogate_key;
BEGIN --select of new surrogate id
select route_parameter_id
into l_new_parameter_id
from ff_route_parameters
where sequence_no = delivered_params.sequence_no
and parameter_name = delivered_params.parameter_name
and route_id = l_new_surrogate_key;
or the parameter naming (or both) be safe and just delete
all parameters for this route and reimport them from scratch
so as to avoid any constraint violations */
/* bug 5501644 */
for r_form3 in c_form3(l_new_surrogate_key) loop
delete ff_fdi_usages_f where formula_id = r_form3.fid;
delete ff_compiled_info_f where formula_id = r_form3.fid;
delete ff_route_parameter_values
where route_parameter_id in (
select route_parameter_id
from ff_route_parameters
where route_id = l_new_surrogate_key);
delete ff_route_parameters
where route_id = l_new_surrogate_key;
select ff_route_parameters_s.nextval
into l_new_parameter_id
from dual;
END; --select of new surrogate id
update hr_s_route_parameters
set route_id = l_new_surrogate_key
, route_parameter_id = l_new_parameter_id
where route_parameter_id = delivered_params.route_parameter_id;
update hr_s_route_parameter_values
set route_parameter_id = l_new_parameter_id
where route_parameter_id = delivered_params.route_parameter_id;
select user_entity_id
into l_new_entity_id
from ff_user_entities
where user_entity_name = delivered_entities.user_entity_name
and nvl(legislation_code,'X') = nvl(delivered_entities.legislation_code,'X')
and route_id = l_new_surrogate_key;
select ff_user_entities_s.nextval
into l_new_entity_id
from dual;
update hr_s_user_entities
set user_entity_id = l_new_entity_id
, route_id = l_new_surrogate_key
where user_entity_id = delivered_entities.user_entity_id;
update hr_s_database_items
set user_entity_id = l_new_entity_id
where user_entity_id = delivered_entities.user_entity_id;
update hr_s_route_parameter_values
set user_entity_id = l_new_entity_id
where user_entity_id = delivered_entities.user_entity_id;
update hr_s_report_format_items_f
set user_entity_id = l_new_entity_id
where user_entity_id = delivered_entities.user_entity_id;
END update_uid;
delete from hr_s_database_items a
where a.user_entity_id in
(select b.user_entity_id
from hr_s_user_entities b
where b.route_id = v_route_id
);
delete from hr_s_route_parameter_values a
where a.user_entity_id in
(select b.user_entity_id
from hr_s_user_entities b
where b.route_id = v_route_id
);
delete from hr_s_user_entities
where route_id = v_route_id;
delete from hr_s_route_context_usages
where route_id = v_route_id;
delete from hr_s_route_parameters
where route_id = v_route_id;
delete from hr_s_routes
where route_id = v_route_id;
select null
from dual
where exists
(select null
from hr_s_application_ownerships a
, fnd_product_installations b
, fnd_application c
where a.key_name = 'ROUTE_ID'
and a.key_value = stu_rec.c_surrogate_key
and a.product_name = c.application_short_name
and c.application_id = b.application_id
and ((b.status = 'I' and c.application_short_name <> 'PQP')
or
(b.status in ('I', 'S') and c.application_short_name = 'PQP')));
-- then this row is not required and may be deleted from the delivery
-- tables.
-- If legislation code and subgroup code are included on the delivery
-- tables, a check must be made to determine if the data is defined for
-- a specific subgroup. If so the subgroup must be 'A'ctive for this
-- installation.
-- A return code of TRUE indicates that the row is required.
-- The exception is raised within this procedure if no rows are returned
-- in this select statement. If no rows are returned then one of the
-- following is true:
-- 1. No ownership parameters are defined.
-- 2. The products, for which owning parameters are defined, are not
-- installed with as status of 'I'.
-- 3. The data is defined for a legislation subgroup that is not active.
IF p_phase <> 1 THEN -- Only perform in phase 1
return TRUE;
-- update dates were differed. Unfortunately this was the case whenever
-- a dump file was recreated, even though the route text was unchanged.
-- This led to all routes being trashed and recreated on the target
-- account, db items being lost etc. RMF 26.09.95.
--
-- Optimizer hint now can trigger update
--
-- from 12.1 also verify route is not a stub (description prefix)
v_route_text clob; -- Used to select the installed route text
v_last_update date; -- Used to select the installed last update
select text, optimizer_hint , description
into v_route_text, v_optimizer_hint, v_route_description
from ff_routes
where route_id = p_route_id;
FUNCTION valid_to_insert RETURN BOOLEAN
---------------------------------------
IS
-- Check to see if the route can be installed.
--
l_number number(9);
select count(*)
into l_number
from ff_routes a
where a.route_name = stu_rec.c_true_key
and a.user_defined_flag = 'Y';
END valid_to_insert;
select null
into l_null_return
from dual
where exists
((select
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
ROUTE_ID,
NOTFOUND_ALLOWED_FLAG,
USER_ENTITY_NAME,
CREATOR_ID,
CREATOR_TYPE,
ENTITY_DESCRIPTION
from hr_s_user_entities
where user_entity_id = v_user_entity_id
MINUS
select
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
ROUTE_ID,
NOTFOUND_ALLOWED_FLAG,
USER_ENTITY_NAME,
CREATOR_ID,
CREATOR_TYPE,
ENTITY_DESCRIPTION
from ff_user_entities
where user_entity_id = v_user_entity_id
)
UNION
(select
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
ROUTE_ID,
NOTFOUND_ALLOWED_FLAG,
USER_ENTITY_NAME,
CREATOR_ID,
CREATOR_TYPE,
ENTITY_DESCRIPTION
from ff_user_entities
where user_entity_id = v_user_entity_id
MINUS
select
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
ROUTE_ID,
NOTFOUND_ALLOWED_FLAG,
USER_ENTITY_NAME,
CREATOR_ID,
CREATOR_TYPE,
ENTITY_DESCRIPTION
from hr_s_user_entities
where user_entity_id = v_user_entity_id
))
or exists
(select user_name,
data_type,
definition_text,
null_allowed_flag,
description
from hr_s_database_items
where user_entity_id = v_user_entity_id
MINUS
select user_name,
data_type,
definition_text,
null_allowed_flag,
description
from ff_database_items
where user_entity_id = v_user_entity_id)
or exists
(select value
from hr_s_route_parameter_values
where user_entity_id = v_user_entity_id
MINUS
select value
from ff_route_parameter_values
where user_entity_id = v_user_entity_id);
select user_entity_name
into ue_name1
from ff_user_entities
where user_entity_id = v_user_entity_id;
select user_entity_name
into ue_name2
from hr_s_user_entities
where user_entity_id = v_user_entity_id;
-- Logic to insert the user entity and all children. If called in pahse one
-- TRUE is returned as soon as a user entity is found that has to be installed.
-- If no user entities are to be installed then FALSE is returned.
cursor c_form(p_ue_id number) is
select distinct fue.formula_id fid
from ff_fdi_usages_f fue
where fue.item_name in (select fdbi.user_name
from ff_database_items fdbi
where fdbi.user_entity_id = p_ue_id);
delete ff_fdi_usages_f where formula_id = r_form.fid;
delete ff_compiled_info_f where formula_id = r_form.fid;
update ff_user_entities
set business_group_id = all_user_entities.business_group_id
,legislation_code = all_user_entities.legislation_code
,route_id = all_user_entities.route_id
,notfound_allowed_flag = all_user_entities.notfound_allowed_flag
,user_entity_name = all_user_entities.user_entity_name
,creator_id = all_user_entities.creator_id
,creator_type = all_user_entities.creator_type
,entity_description = all_user_entities.entity_description
,last_update_date = all_user_entities.last_update_date
,last_updated_by = all_user_entities.last_updated_by
,last_update_login = all_user_entities.last_update_login
,created_by = all_user_entities.created_by
,creation_date = all_user_entities.creation_date
where user_entity_id = all_user_entities.user_entity_id;
insert into ff_user_entities
(user_entity_id
,business_group_id
,legislation_code
,route_id
,notfound_allowed_flag
,user_entity_name
,creator_id
,creator_type
,entity_description
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(all_user_entities.user_entity_id
,all_user_entities.business_group_id
,all_user_entities.legislation_code
,all_user_entities.route_id
,all_user_entities.notfound_allowed_flag
,all_user_entities.user_entity_name
,all_user_entities.creator_id
,all_user_entities.creator_type
,all_user_entities.entity_description
,all_user_entities.last_update_date
,all_user_entities.last_updated_by
,all_user_entities.last_update_login
,all_user_entities.created_by
,all_user_entities.creation_date
);
update ff_database_items
set data_type = all_db_items.data_type
,definition_text = all_db_items.definition_text
,null_allowed_flag = all_db_items.null_allowed_flag
,description = all_db_items.description
,last_update_date = all_db_items.last_update_date
,last_updated_by = all_db_items.last_updated_by
,last_update_login = all_db_items.last_update_login
,created_by = all_db_items.created_by
,creation_date = all_db_items.creation_date
where user_name = all_db_items.user_name
and user_entity_id = all_db_items.user_entity_id;
insert into ff_database_items
(user_name
,user_entity_id
,data_type
,definition_text
,null_allowed_flag
,description
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
VALUES
(all_db_items.user_name
,all_db_items.user_entity_id
,all_db_items.data_type
,all_db_items.definition_text
,all_db_items.null_allowed_flag
,all_db_items.description
,all_db_items.last_update_date
,all_db_items.last_updated_by
,all_db_items.last_update_login
,all_db_items.created_by
,all_db_items.creation_date
);
update ff_route_parameter_values
set value = pvalues.value
,last_update_date = pvalues.last_update_date
,last_updated_by = pvalues.last_updated_by
,last_update_login = pvalues.last_update_login
,created_by = pvalues.created_by
,creation_date = pvalues.creation_date
where route_parameter_id = pvalues.route_parameter_id
and user_entity_id = pvalues.user_entity_id;
insert into ff_route_parameter_values
(route_parameter_id
,user_entity_id
,value
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
VALUES
(pvalues.route_parameter_id
,pvalues.user_entity_id
,pvalues.value
,pvalues.last_update_date
,pvalues.last_updated_by
,pvalues.last_update_login
,pvalues.created_by
,pvalues.creation_date
);
PROCEDURE delete_route_form_usage
---------------------------------
IS
cursor c_form2(p_route_id number) is
select /*+ LEADING (FUE,FDBI) */
distinct formula_id fid
from
ff_user_entities fue,
ff_database_items fdbi,
ff_fdi_usages_f fdi
where fdi.item_name = fdbi.user_name
and fdbi.user_entity_id = fue.user_entity_id
and fue.route_id = p_route_id;
delete ff_fdi_usages_f where formula_id = r_form2.fid;
delete ff_compiled_info_f where formula_id = r_form2.fid;
END delete_route_form_usage;
PROCEDURE insert_route
----------------------
IS
-- Logic to insert or update a route, depending upon whether it exists
-- already in the live tables
BEGIN
update ff_routes
set user_defined_flag = stu_rec.user_defined_flag
, description = stu_Rec.description
, text = stu_rec.text
, last_update_date = stu_rec.last_update_date
, last_updated_by = stu_rec.last_updated_by
, last_update_login = stu_rec.last_update_login
, created_by = stu_rec.created_by
, creation_date = stu_rec.creation_date
, optimizer_hint = stu_rec.optimizer_hint
where route_id = stu_rec.c_Surrogate_key;
insert into ff_Routes
(route_id
,route_name
,user_defined_flag
,description
,text
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,optimizer_hint
)
values
(stu_rec.c_surrogate_key
,stu_rec.c_true_key
,stu_rec.user_defined_flag
,stu_rec.description
,stu_rec.text
,stu_rec.last_update_date
,stu_rec.last_updated_by
,stu_rec.last_update_login
,stu_rec.created_by
,stu_rec.creation_date
,stu_rec.optimizer_hint
);
END insert_route;
select null
from ff_contexts
where context_id = c_context_id;
select null
from ff_route_parameters
where route_parameter_id = c_route_parameter_id;
select distinct null
from ff_route_context_usages
where route_id = c_route_id
and sequence_no = c_sequence_no
and context_id = c_context_id;
IF NOT valid_to_insert THEN
return;
IF NOT valid_to_insert THEN
return;
delete_route_form_usage;
insert_route;
delete from ff_user_entities
where creator_type in ('B', 'RB')
and route_id = stu_rec.c_surrogate_key;
delete ff_route_context_usages
where route_id = stu_rec.c_surrogate_key;
insert into ff_route_context_usages
(route_id
,context_id
,sequence_no
)
values
(context_usages.route_id
,context_usages.context_id
,context_usages.sequence_no
);
update ff_route_parameters
set ROUTE_ID = stu_rec.c_surrogate_key
,DATA_TYPE = r_hrsrp.DATA_TYPE
,PARAMETER_NAME = r_hrsrp.PARAMETER_NAME
,SEQUENCE_NO = r_hrsrp.SEQUENCE_NO
where ROUTE_PARAMETER_ID = r_hrsrp.ROUTE_PARAMETER_ID;
insert into ff_route_parameters
(ROUTE_PARAMETER_ID
,ROUTE_ID
,DATA_TYPE
,PARAMETER_NAME
,SEQUENCE_NO
)
values
(r_hrsrp.ROUTE_PARAMETER_ID,
stu_rec.c_surrogate_key,
r_hrsrp.DATA_TYPE,
r_hrsrp.PARAMETER_NAME,
r_hrsrp.SEQUENCE_NO);
select 'Y'
into l_skip_route_upd
from pay_patch_status
where patch_name = 'HRGLOBAL_DBG_NRCOD';
update_uid;
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select distinct name
, legislation_code c_leg_code
from hr_s_functions;
-- Cursor to select distinct functions
select *
from hr_s_functions
where name = f_name
and nvl(legislation_code, 'X') = nvl(c_leg_code, 'X');
select *
from hr_s_function_context_usages
where function_id = f_id;
hr_legislation.insert_hr_stu_exceptions('ff_functions'
, 0
, exception_type
, stu_rec.name);
-- This procedure either deletes from the delivered account,
-- parameter of 'D', or from the live account, parameter of 'I'.
-- If the delivered details are being deleted the explicit deletes
-- from all child tables are required, since the cascade constraint
-- will not be delivered with these tables.
-- When deleting from the live account, the cascade delete can
-- be relied upon.
IF target = 'D' THEN
delete from hr_s_function_context_usages a
where exists
(select null
from hr_s_functions b
where b.function_id = a.function_id
and b.name = stu_rec.name
and nvl(b.legislation_code,'X')=nvl(stu_rec.c_leg_code,'X')
);
delete from hr_s_function_parameters a
where exists
(select null
from hr_s_functions b
where b.function_id = a.function_id
and b.name = stu_rec.name
and nvl(b.legislation_code,'X')=nvl(stu_rec.c_leg_code,'X')
);
delete from hr_s_functions
where name = stu_rec.name
and nvl(legislation_code,'X')=nvl(stu_rec.c_leg_code,'X');
-- Delete from live account using the cascade delete
delete from ff_functions
where name = stu_rec.name
and nvl(legislation_code,'X')=nvl(stu_rec.c_leg_code,'X');
PROCEDURE insert_delivered
--------------------------
IS
-- Check if a delivered row is needed and insert into the
-- live tables if it is
v_inst_update date; -- Holds update details of installed row
select distinct null
into l_null_return
from ff_functions a
where a.name = stu_rec.name
and a.business_group_id is not null
and exists (select null from per_business_groups b
where b.business_group_id = a.business_group_id
and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
select distinct null
into l_null_return
from ff_functions
where name = stu_rec.name
and nvl(legislation_code,'X') <> nvl(stu_rec.c_leg_code,'X')
and (
legislation_code is null
or stu_rec.c_leg_code is null
);
-- functions will be be deleted and replaced with the delivered
-- rows.
-- The routine check_parents validates foreign key references and
-- ensures referential integrity. The routine checks to see if the
-- parents of a given row have been transfered to the live tables.
-- This may only be called in phase two since in phase one all
-- parent rows will remain in the delivery tables.
-- After the above checks only data that has been chanegd or is new
-- will be left in the delivery tables.
-- The last step of this transfer, in phase 2, is to delete the now
-- transfered row from the delivery tables.
-- Before the update/insert goes ahead, ensure all child rows
-- are removed so the refrsh of child rows is simple.
remove('I');
select ff_functions_s.nextval
into l_new_surrogate_key
from dual;
insert into ff_functions
(function_id
,business_group_id
,legislation_code
,class
,name
,alias_name
,data_type
,definition
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values (l_new_surrogate_key
,null
,each_func.legislation_code
,each_func.class
,each_func.name
,each_func.alias_name
,each_func.data_type
,each_func.definition
,each_func.last_update_date
,each_func.last_updated_by
,each_func.last_update_login
,each_func.created_by
,each_func.creation_date
);
insert into ff_function_parameters
(function_id
,sequence_number
,class
,continuing_parameter
,data_type
,name
,optional
)
select l_new_surrogate_key
, sequence_number
, class
, continuing_parameter
, data_type
, name
, optional
from hr_s_function_parameters
where function_id = each_func.function_id;
select null
into l_null_return
from ff_contexts
where context_id = child_usages.context_id;
insert into ff_function_context_usages
(function_id
,sequence_number
,context_id
)
values
(l_new_surrogate_key
,child_usages.sequence_number
,child_usages.context_id
);
END insert_delivered;
insert_delivered;
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select qp_report_id c_surrogate_key
, formula_id
, formula_type_id
, qp_report_name c_true_key
, business_group_id
, legislation_code c_leg_code
, qp_altered_formula
, qp_description
, qp_text
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, rowid
from hr_s_qp_reports;
hr_legislation.insert_hr_stu_exceptions('ff_qp_reports'
, stu_rec.c_surrogate_key
, exception_type
, stu_rec.c_true_key);
-- 1. Where the newly select sequence value conflicts with values
-- in the STU tables.
-- 2. Where selected surrogate keys, from the installed tables,
-- conflict with other rows in the STU tables.
--
-- Both of the above scenario's are tested for.
-- The first is a simple match, where if a value is detected in the
-- STU tables and the installed tables then a conflict is detected. In
-- This instance all STU surrogate keys, for this table, are updated.
-- The second is tested for using the sequences.
-- If the next value from the live sequence is within the range of
-- delivered surrogate id's then the live sequence must be incremented.
-- If no action is taken, then duplicates may be introduced into the
-- delivered tables, and child rows may be totally invalidated.
BEGIN
BEGIN --check that the installed id's will not conflict
--with the delivered values
select distinct null
into l_null_return
from ff_qp_reports a
where exists
(select null
from hr_s_qp_reports b
where a.qp_report_id = b.qp_report_id
);
--update all qp_report_id's to remove conflict
update hr_s_qp_reports
set qp_report_id = qp_report_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'QP_REPORT_ID';
select min(qp_report_id) - (count(*) *3)
, max(qp_report_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_qp_reports;
select ff_qp_reports_s.nextval
into v_sequence_number
from dual;
PROCEDURE update_uid
--------------------
IS
-- Update surrogate UID and all occurrences in child rows
BEGIN
BEGIN
select distinct qp_report_id
into l_new_surrogate_key
from ff_qp_reports
where qp_report_name = stu_rec.c_true_key
and business_group_id is null
and ( (legislation_code is null and stu_rec.c_leg_code is null)
or (legislation_code = stu_rec.c_leg_code) );
select ff_qp_reports_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_qp_reports
set qp_report_id = l_new_surrogate_key
where qp_report_id = stu_rec.c_surrogate_key;
update hr_s_application_ownerships
set key_value = to_char(l_new_surrogate_key)
where key_value = to_char(stu_rec.c_surrogate_key)
and key_name = 'QP_REPORT_ID';
END update_uid;
delete from hr_s_qp_reports
where rowid = stu_rec.rowid;
-- then this row is not required and may be deleted from the delivery
-- tables.
-- If legislation code and subgroup code are included on the delivery
-- tables, a check must be made to determine if the data is defined for
-- a specific subgroup. If so the subgroup must be 'A'ctive for this
-- installation.
-- A return code of TRUE indicates that thhe row is required.
-- The exception is raised within this procedure if no rows are returned
-- in this select statement. If no rows are returned then one of the
-- following is true:
-- 1. No ownership parameters are defined.
-- 2. The products, for which owning parameters are defined, are not
-- installed with as status of 'I'.
-- 3. The data is defined for a legislation subgroup that is not active.
BEGIN
IF p_phase <> 1 THEN -- Only perform in phase 1
return TRUE;
select null
into l_null_return
from dual
where exists
(select null
from hr_s_application_ownerships a
, fnd_product_installations b
, fnd_application c
where a.key_name = 'QP_REPORT_ID'
and a.key_value = stu_rec.c_surrogate_key
and a.product_name = c.application_short_name
and c.application_id = b.application_id
and ((b.status = 'I' and c.application_short_name <> 'PQP')
or
(b.status in ('I', 'S') and c.application_short_name = 'PQP')));
-- every foriegn key. The first select from the delivery tables.
-- If a row is founnd then the installation of the parent must have
-- failed, and this installation must not go ahead. If no data is
-- found, ie: an exception is raised, the installation is valid.
-- The second check looks for a row in the live tables. If no rows
-- are returned then this installation is invalid, since this means
-- that the parent referenced by this row is not present in the
-- live tables.
-- The distinct is used in case the parent is date effective and many rows
-- may be returned by the same parent id.
BEGIN
-- Start parent checking against formula types
BEGIN
-- Checking the delivery account
select distinct null
into l_null_return
from hr_s_formula_types
where formula_type_id = stu_rec.formula_type_id;
select null
into l_null_return
from ff_formula_types
where formula_type_id = stu_rec.formula_type_id;
select distinct null
into l_null_return
from hr_s_formulas_f
where formula_id = stu_rec.formula_id;
select distinct null
into l_null_return
from ff_formulas_f
where formula_id = stu_rec.formula_id;
-- Check if a delivered row is needed and insert into the
-- live tables if it is
v_inst_update date; -- Holds update details of installed row
select distinct null
into l_null_return
from ff_qp_reports a
where a.qp_report_name = stu_rec.c_true_key
and a.business_group_id is not null
and exists (select null from per_business_groups b
where b.business_group_id = a.business_group_id
and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
select distinct null
into l_null_return
from ff_qp_reports
where qp_report_name = stu_rec.c_true_key
and nvl(legislation_code,'X') <> nvl(stu_rec.c_leg_code,'X')
and (legislation_code is null
or stu_rec.c_leg_code is null );
-- If the procedure is called in phase 2, then the live row is updated
-- with the values on the delivered row.
-- The routine check_parents validates foreign key references and
-- ensures referential integrity. The routine checks to see if the
-- parents of a given row have been transfered to the live tables.
-- This may only be called in phase two since in phase one all
-- parent rows will remain in the delivery tables.
-- After the above checks only data that has been chanegd or is new
-- will be left in the delivery tables. At this stage if the row is
-- already present then it must be updated to ensure referential
-- integrity. Therefore an update will be performed and if SQL%FOUND
-- is FALSE an insert will be performed.
-- The last step of the transfer, in phase 2, is to delete the now
-- transfered row from the delivery tables.
IF p_phase = 1 THEN
return;
update ff_qp_reports
set formula_id = stu_rec.formula_id
, formula_type_id = stu_rec.formula_type_id
, qp_report_name = to_char(stu_rec.c_surrogate_key)
, business_group_id = null
, legislation_code = stu_rec.c_leg_code
, qp_altered_formula = stu_rec.qp_altered_formula
, qp_description = stu_rec.qp_description
, qp_text = stu_rec.qp_text
, last_update_date = stu_rec.last_update_date
, last_updated_by = stu_rec.last_updated_by
, last_update_login = stu_rec.last_update_login
, created_by = stu_rec.created_by
, creation_date = stu_rec.creation_date
where qp_report_id = stu_rec.c_surrogate_key;
insert into ff_qp_reports
(qp_report_id
,formula_id
,formula_type_id
,qp_report_name
,business_group_id
,legislation_code
,qp_altered_formula
,qp_description
,qp_text
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(stu_rec.c_surrogate_key
,stu_rec.formula_id
,stu_rec.formula_type_id
,stu_rec.c_true_key
,null
,stu_rec.c_leg_code
,stu_rec.qp_altered_formula
,stu_rec.qp_description
,stu_rec.qp_text
,stu_rec.last_update_date
,stu_rec.last_updated_by
,stu_rec.last_update_login
,stu_rec.created_bY
,stu_rec.creation_date
);
update_uid;
-- Select statement used for the main loop. Each row return is used
-- as the commit unit, since each true primary key may have many date
-- effective rows for it.
-- The selected primary key is then passed into the second driving
-- cursor statement as a parameter, and all date effective rows for
-- this primary key are then selected.
select max(effective_end_date) c_end
, global_id c_surrogate_key
, global_name c_true_key
, legislation_code
from hr_s_globals_f
group by global_id
, global_name
, legislation_code;
-- Selects all date effective rows for the current true primary key
-- The primary key has already been selected using the above cursor.
-- This cursor accepts the primary key as a parameter and selects all
-- date effective rows for it.
select *
from hr_s_globals_f
where global_id = pc_global_id;
select distinct ffu.formula_id fid
from ff_fdi_usages_f ffu
where ffu.item_name in (select fdbi.user_name
from ff_database_items fdbi,
ff_user_entities ffue
where fdbi.user_entity_id = ffue.user_entity_id
and ffue.creator_id = p_global_id
and ffue.creator_type = 'S');
-- Subprogram to delete a row from the delivery tables, and all child
-- application ownership rows
BEGIN
delete from hr_s_globals_f
where global_id = v_id;
-- 1. Where the newly select sequence value conflicts with values
-- in the STU tables.
-- 2. Where selected surrogate keys, from the installed tables,
-- conflict with other rows in the STU tables.
--
-- Both of the above scenario's are tested for.
-- The first is a simple match, where if a value is detected in the
-- STU tables and the installed tables then a conflict is detected. In
-- This instance all STU surrogate keys, for this table, are updated.
-- The second is tested for using the sequences.
-- If the next value from the live sequence is within the range of
-- delivered surrogate id's then the live sequence must be incremented.
-- If no action is taken, then duplicates may be introduced into the
-- delivered tables, and child rows may be totally invalidated.
BEGIN
BEGIN --check that the installed id's will not conflict
--with the delivered values
select distinct null
into l_null_return
from ff_globals_f a
where exists
(select null
from hr_s_globals_f b
where a.global_id = b.global_id
);
--update all global_id's to remove conflict
update /*+NO_INDEX*/ hr_s_globals_f
set global_id = global_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'GLOBAL_ID';
select min(global_id) - (count(*) *3)
, max(global_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_globals_f;
select ff_globals_s.nextval
into v_sequence_number
from dual;
PROCEDURE update_uid
--------------------
IS
-- Subprogram to update surrogate UID and all occurrences in child rows
BEGIN
-- See if this primary key is already installed. If so then the sorrogate
-- key of the delivered row must be updated to the value in the installed
-- tables. If the row is not already present then select the next value
-- from the sequence. In either case all rows for this primary key must
-- be updated, as must all child references to the old surrogate uid.
BEGIN
select distinct global_id
into l_new_global_id
from ff_globals_f
where global_name = r_distinct.c_true_key
and business_Group_id is null
and ((legislation_code is NULL and r_distinct.legislation_code is NULL)
or (r_distinct.legislation_code=legislation_code));
select ff_globals_s.nextval
into l_new_global_id
from dual;
update hr_s_globals_f
set global_id = l_new_global_id
where global_id = r_distinct.c_surrogate_key;
update hr_s_application_ownerships
set key_value = to_char(l_new_global_id)
where key_value = to_char(r_distinct.c_surrogate_key)
and key_name = 'GLOBAL_ID';
END update_uid;
hr_legislation.insert_hr_stu_exceptions('ff_globals_f'
, r_distinct.c_surrogate_key
, exception_type
, r_distinct.c_true_key);
select distinct null
from ff_globals_f a
where a.global_name = r_distinct.c_true_key
and a.business_group_id is not null
and exists (select null from per_business_groups b
where b.business_group_id = a.business_group_id
and b.legislation_code = nvl(r_distinct.legislation_code,b.legislation_code));
select distinct null
from ff_globals_f
where global_name = r_distinct.c_true_key
and nvl(legislation_code,'X')<>nvl(r_distinct.legislation_code,'X')
and (legislation_code is null
or r_distinct.legislation_code is null );
-- tables the row is either deleted or not. If the delivered row
-- is 'stamped' with a legislation subgroup, then a chweck must be
-- made to see if that subgroup is active or not. This check only
-- needs to be performed in phase 1, since once this decision is
-- made, it is pontless to perform this logic again.
-- in this select statement. If no rows are returned then one of the
-- following is true:
-- 1. No ownership parameters are defined.
-- 2. The products, for which owning parameters are defined, are not
-- installed with as status of 'I'.
-- 3. The data is defined for a legislation subgroup that is not active.
IF p_phase <> 1 THEN return TRUE; END IF;
select null
into l_null_return
from dual
where exists (select null from hr_s_application_ownerships a
, fnd_product_installations b
, fnd_application c
where a.key_name = 'GLOBAL_ID'
and a.key_value = r_distinct.c_surrogate_key
and a.product_name = c.application_short_name
and c.application_id = b.application_id
and ((b.status = 'I' and c.application_short_name <> 'PQP')
or
(b.status in ('I', 'S') and c.application_short_name = 'PQP')));
-- Get new surrogate id and update child references
update_uid;
delete from hr_s_globals_f
where global_id = l_new_global_id
and not exists
((select
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
DATA_TYPE,
GLOBAL_NAME,
GLOBAL_DESCRIPTION,
GLOBAL_VALUE
from hr_s_globals_f
where global_id = l_new_global_id
MINUS
select
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
DATA_TYPE,
GLOBAL_NAME,
GLOBAL_DESCRIPTION,
GLOBAL_VALUE
from ff_globals_f
where global_id = l_new_global_id
)
UNION
(select
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
DATA_TYPE,
GLOBAL_NAME,
GLOBAL_DESCRIPTION,
GLOBAL_VALUE
from ff_globals_f
where global_id = l_new_global_id
MINUS
select
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
DATA_TYPE,
GLOBAL_NAME,
GLOBAL_DESCRIPTION,
GLOBAL_VALUE
from hr_s_globals_f
where global_id = l_new_global_id
))
and exists (select distinct null
from ff_user_entities u,
ff_database_items d,
ff_route_parameters rp,
ff_route_parameter_values rpv
where u.user_entity_name = global_name || '_GLOBAL_UE'
and u.user_entity_id = rpv.user_entity_id
and d.user_entity_id = u.user_entity_id
and rpv.route_parameter_id = rp.route_parameter_id
and rpv.value = to_char(l_new_global_id));
delete ff_fdi_usages_f where formula_id = r_global.fid;
delete ff_compiled_info_f where formula_id = r_global.fid;
delete ff_route_parameter_values
where user_entity_id = (select user_entity_id
from ff_user_entities
where user_entity_name = r_distinct.c_true_key || '_GLOBAL_UE');
delete from ff_globals_f
where global_id = r_distinct.c_surrogate_key;
insert into ff_globals_f
(GLOBAL_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,DATA_TYPE
,GLOBAL_NAME
,GLOBAL_DESCRIPTION
,GLOBAL_VALUE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE)
values
(r_each_row.GLOBAL_ID
,r_each_row.EFFECTIVE_START_DATE
,r_each_row.EFFECTIVE_END_DATE
,r_each_row.BUSINESS_GROUP_ID
,r_each_row.LEGISLATION_CODE
,r_each_row.DATA_TYPE
,r_each_row.GLOBAL_NAME
,r_each_row.GLOBAL_DESCRIPTION
,r_each_row.GLOBAL_VALUE
,r_each_row.LAST_UPDATE_DATE
,r_each_row.LAST_UPDATED_BY
,r_each_row.LAST_UPDATE_LOGIN
,r_each_row.CREATED_BY
,r_each_row.CREATION_DATE);
core_selected NUMBER;
select count(*)
into core_selected
from hr_legislation_installations
where legislation_code is null
and action in ('I', 'U', 'F');