[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 classification_name c_true_key
, rowid
, classification_id c_surrogate_key
, legislation_code c_leg_code
, legislation_subgroup c_leg_sgrp
, business_group_id
, description
, costing_debit_or_credit
, default_high_priority
, default_low_priority
, default_priority
, distributable_over_flag
, non_payments_flag
, parent_classification_id
, costable_flag
, create_by_default_flag
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, balance_initialization_flag
, FREQ_RULE_ENABLED
from hr_s_element_classifications
order by parent_classification_id desc;
hr_legislation.insert_hr_stu_exceptions('pay_element_classifications'
, 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 pay_element_classifications a
where exists
(select null
from hr_s_element_classifications b
where a.classification_id = b.classification_id
);
--update all classification_id's to remove conflict
update /*+NO_INDEX*/ hr_s_element_classifications
set classification_id = classification_id - 50000000
,parent_classification_id = parent_classification_id - 50000000;
update /*+NO_INDEX*/ hr_s_BALANCE_CLASSIFICATIONS
set classification_id = classification_id - 50000000;
update /*+NO_INDEX*/ hr_s_ELEMENT_TYPES_F
set classification_id = classification_id - 50000000;
update /*+NO_INDEX*/ hr_s_ELE_CLASSN_RULES
set classification_id = classification_id - 50000000;
update /*+NO_INDEX*/ hr_s_SUB_CLASSN_RULES_F
set classification_id = classification_id - 50000000;
update /*+NO_INDEX*/ hr_s_TAXABILITY_RULES
set classification_id = classification_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'CLASSIFICATION_ID';
select min(classification_id) - (count(*) *3)
, max(classification_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_element_classifications;
select pay_element_classifications_s.nextval
into v_sequence_number
from dual;
-- 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.
IF stu_rec.parent_classification_id is null THEN
-- No need to check parent
return TRUE;
select distinct null
into l_null_return
from hr_s_element_classifications
where classification_id = stu_rec.parent_classification_id;
select null
into l_null_return
from pay_element_classifications
where classification_id = stu_rec.parent_classification_id;
PROCEDURE update_uid
--------------------
IS
-- Subprogram to update surrogate UID and all occurrences in child rows
BEGIN
BEGIN
select distinct classification_id
into l_new_surrogate_key
from pay_element_classifications
where classification_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 pay_element_classifications_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_element_classifications
set classification_id = l_new_surrogate_key
where classification_id = stu_rec.c_surrogate_key;
update hr_s_element_classifications
set parent_classification_id = l_new_surrogate_key
where parent_classification_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 = 'CLASSIFICATION_ID';
update hr_s_element_types_f
set classification_id = l_new_surrogate_key
where classification_id = stu_rec.c_surrogate_key;
update hr_s_balance_classifications
set classification_id = l_new_surrogate_key
where classification_id = stu_rec.c_surrogate_key;
update hr_s_sub_classn_rules_f
set classification_id = l_new_surrogate_key
where classification_id = stu_rec.c_surrogate_key;
update hr_s_ele_classn_rules
set classification_id = l_new_surrogate_key
where classification_id = stu_rec.c_surrogate_key;
update hr_s_taxability_rules
set classification_id = l_new_surrogate_key
where classification_id = stu_rec.c_surrogate_key;
END update_uid;
delete from hr_s_element_classifications
where rowid = stu_rec.rowid;
delete from hr_s_taxability_rules
where classification_id = stu_rec.c_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.
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 = 'CLASSIFICATION_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')));
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 = 'CLASSIFICATION_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')))
and exists (select null from hr_legislation_subgroups d
where d.legislation_code = stu_rec.c_leg_code
and d.legislation_subgroup = stu_rec.c_leg_sgrp
and d.active_inactive_flag = 'A'
);
-- Check if a delivered row is needed and insert into the
-- live tables if it is
BEGIN
BEGIN
-- Perform a check to see if the primary key has been created within
-- a visible business group. Ie: the business group is for the same
-- legislation as the delivered row, or the delivered row has a null
-- legislation. If no rows are returned then the primary key has not
-- already been created by a user.
select distinct null
into l_null_return
from pay_element_classifications a
where a.classification_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 pay_element_classifications
where classification_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 )
and business_group_id 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 pay_element_classifications
set classification_name = stu_rec.c_true_key
, legislation_code = stu_rec.c_leg_code
, legislation_subgroup = stu_rec.c_leg_sgrp
, business_group_id = stu_rec.business_group_id
, description = stu_rec.description
, costing_debit_or_credit = stu_rec.costing_debit_or_credit
, default_high_priority = stu_rec.default_high_priority
, default_low_priority = stu_rec.default_low_priority
, default_priority = stu_rec.default_priority
, distributable_over_flag = stu_rec.distributable_over_flag
, non_payments_flag = stu_rec.non_payments_flag
, parent_classification_id = stu_rec.parent_classification_id
, costable_flag = stu_rec.costable_flag
, create_by_default_flag = stu_rec.create_by_default_flag
, 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
, balance_initialization_flag = stu_rec.balance_initialization_flag
, FREQ_RULE_ENABLED = stu_rec.FREQ_RULE_ENABLED
where classification_id = stu_rec.c_surrogate_key;
insert into pay_element_classifications
(classification_name
,classification_id
,legislation_code
,legislation_subgroup
,business_group_id
,description
,costing_debit_or_credit
,default_high_priority
,default_low_priority
,default_priority
,distributable_over_flag
,non_payments_flag
,parent_classification_id
,costable_flag
,create_by_default_flag
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,balance_initialization_flag
,FREQ_RULE_ENABLED
)
values
(stu_rec.c_true_key
,stu_rec.c_surrogate_key
,stu_rec.c_leg_code
,stu_rec.c_leg_sgrp
,stu_rec.business_group_id
,stu_rec.description
,stu_rec.costing_debit_or_credit
,stu_rec.default_high_priority
,stu_rec.default_low_priority
,stu_rec.default_priority
,stu_rec.distributable_over_flag
,stu_rec.non_payments_flag
,stu_rec.parent_classification_id
,stu_rec.costable_flag
,stu_rec.create_by_default_flag
,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.balance_initialization_flag
,stu_rec.FREQ_RULE_ENABLED);
update_uid;
-- Select distinct element names. The element name can no longer be
-- guarenteed to be unique. This cursor selects all distinct names
-- for the next cusrsor to select distinct element_type_id's from.
select distinct element_name
from hr_s_element_types_f;
-- This row is then used to select all date effective rows for this id.
select max(effective_end_date) c_end
, min(effective_start_date) c_start
, element_type_id c_surrogate_key
, element_name c_true_key
, legislation_code
, legislation_subgroup
, nvl(new_element_type_flag, 'Y') new_element_type_flag
from hr_s_element_types_f
where element_name = pc_ele_name
group by element_type_id
, element_name
, legislation_code
, legislation_subgroup
, nvl(new_element_type_flag, 'Y');
-- 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_element_types_f
where element_type_id = pc_element_type_id;
select *
from hr_s_sub_classn_rules_f
where element_type_id = pc_element_id;
-- Used for the update of uid's.
--
-- #346366. Also pull back the assignment_status_type_id, as we
-- could have different rules for different statuses for the
-- same element type.
--
select distinct status_processing_rule_id s_rule_id,
assignment_status_type_id,processing_rule
from hr_s_status_processing_rules_f
where element_Type_id = pc_element_id;
-- Retrieves full details of processing rules for the insertiion into
-- live tables.
select *
from hr_s_status_processing_rules_f
where status_processing_rule_id = pc_stat_rule_id;
select *
from hr_s_formula_result_rules_f
where status_processing_rule_id = pc_stat_rule_id;
select distinct formula_result_rule_id
from hr_s_formula_result_rules_f
where status_processing_rule_id = pc_stat_rule_id;
select distinct input_value_id
, name
, value_set_name
, new_input_value_flag
from hr_s_input_values_f
where element_Type_id = pc_element_id;
-- values selected. This saves the need for all sub procedures to have
-- a myriad of parameters passed. The cursors are controlled in FOR
-- cursor LOOPs. When a row is returned the whole record is copied into
-- these record definitions.
r_distinct c_distinct_element%ROWTYPE;
-- 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 pay_element_types_f a
where exists
(select null
from hr_s_element_types_f b
where a.element_type_id = b.element_type_id
);
--update all element_type_id's to remove conflict
update /*+NO_INDEX*/ hr_s_ELEMENT_TYPES_F
set element_type_id = element_type_id - 50000000,
retro_summ_ele_id = retro_summ_ele_id - 50000000;
update /*+NO_INDEX*/ hr_s_ELEMENT_TYPE_RULES
set element_type_id = element_type_id - 50000000;
update /*+NO_INDEX*/ hr_s_FORMULA_RESULT_RULES_F
set element_type_id = element_type_id - 50000000;
update /*+NO_INDEX*/ hr_s_INPUT_VALUES_F
set element_type_id = element_type_id - 50000000;
update /*+NO_INDEX*/ hr_s_STATUS_PROCESSING_RULES_F
set element_type_id = element_type_id - 50000000;
update /*+NO_INDEX*/ hr_s_SUB_CLASSN_RULES_F
set element_type_id = element_type_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'ELEMENT_TYPE_ID';
select distinct null
into l_null_return
from pay_input_values_f a
where exists
(select null
from hr_s_input_values_f b
where a.input_value_id = b.input_value_id
);
--update all input_value_id's to remove conflict
update /*+NO_INDEX*/ hr_s_INPUT_VALUES_F
set input_value_id = input_value_id - 50000000;
update /*+NO_INDEX*/ hr_s_BALANCE_FEEDS_F
set input_value_id = input_value_id - 50000000;
update /*+NO_INDEX*/ hr_s_FORMULA_RESULT_RULES_F
set input_value_id = input_value_id - 50000000;
update /*+NO_INDEX*/ hr_s_balance_types
set input_value_id = input_value_id - 50000000;
-- the update STU_FORMULA_RESULT_RULES_F statement immediately
-- above, resulting in the input_value_id being decremented
-- twice, thereby breaking the fkey link.
--
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
select distinct null
into l_null_return
from pay_status_processing_rules_f a
where exists
(select null
from hr_s_status_processing_rules_f b
where a.status_processing_rule_id=b.status_processing_rule_id
);
--update all status_processing_rule_id's to remove conflict
update hr_s_FORMULA_RESULT_RULES_F
set status_processing_rule_id=status_processing_rule_id-50000000;
update hr_s_STATUS_PROCESSING_RULES_F
set status_processing_rule_id=status_processing_rule_id-50000000;
select distinct null
into l_null_return
from pay_formula_result_rules_f a
where exists
(select null
from hr_s_formula_result_rules_f b
where a.formula_result_rule_id = b.formula_result_rule_id
);
--update all formula_result_rule_id's to remove conflict
update /*+NO_INDEX*/ hr_s_FORMULA_RESULT_RULES_F
set formula_result_rule_id = formula_result_rule_id - 50000000;
select distinct null
into l_null_return
from pay_sub_classification_rules_f a
where exists
(select null
from hr_s_sub_classn_rules_f b
where a.sub_classification_rule_id=b.sub_classification_rule_id
);
--update all sub_classification_rule_id's to remove conflict
update /*+NO_INDEX*/ hr_s_sub_classn_rules_f
set sub_classification_rule_id=sub_classification_rule_id-50000000;
select min(element_type_id) - (count(*) *3)
, max(element_type_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_element_types_f;
select pay_element_types_s.nextval
into v_sequence_number
from dual;
select pay_element_types_s.nextval
into v_sequence_number
from dual;
select min(input_value_id) - (count(*) *3)
, max(input_value_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_input_values_f;
select pay_input_Values_s.nextval
into v_sequence_number
from dual;
select min(status_processing_rule_id) - (count(*) *3)
, max(status_processing_rule_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_status_processing_rules_f;
select pay_status_processing_rules_s.nextval
into v_sequence_number
from dual;
select min(formula_result_rule_id) - (count(*) *3)
, max(formula_result_rule_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_formula_result_rules_f;
select pay_formula_result_rules_s.nextval
into v_sequence_number
from dual;
select min(sub_classification_rule_id) - (count(*) *3)
, max(sub_classification_rule_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_sub_classn_rules_f;
select pay_sub_classification_rules_s.nextval
into v_sequence_number
from dual;
hr_legislation.insert_hr_stu_exceptions('pay_element_types_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_element_types_f
where element_type_id = v_id;
delete from hr_s_sub_classn_rules_f
where element_type_id = v_id;
delete from hr_s_input_values_f
where element_type_id = v_id;
delete from hr_s_formula_result_rules_f a
where exists
(select null
from hr_s_status_processing_rules_f b
where b.status_processing_rule_id = a.status_processing_rule_id
and b.element_type_id = v_id
);
delete from hr_s_status_processing_rules_f
where element_type_id = v_id;
PROCEDURE update_uid
--------------------
IS
-- Subprogram to update surrogate UID and all occurrences in child rows
v_new_sub_class_id number(15);
-- 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 element_type_id
into l_new_element_type_id
from pay_element_types_f
where replace(ltrim(rtrim(upper(element_name))), ' ', '_') =
replace(ltrim(rtrim(upper(r_distinct.c_true_key))), ' ', '_')
and business_Group_id is null
and legislation_code = r_distinct.legislation_code;
select pay_element_types_s.nextval
into l_new_element_type_id
from dual;
update hr_s_element_types_f
set element_type_id = l_new_element_type_id,
new_element_type_flag = v_new_element_type_flag
where element_type_id = r_distinct.c_surrogate_key;
update hr_s_element_types_f
set retro_summ_ele_id = l_new_element_type_id
where retro_summ_ele_id = r_distinct.c_surrogate_key;
update hr_s_element_type_rules
set element_type_id = l_new_element_type_id
where element_type_id = r_distinct.c_surrogate_key;
update hr_s_formula_result_rules_f
set element_type_id = l_new_element_type_id
where element_type_id = r_distinct.c_surrogate_key;
update hr_s_application_ownerships
set key_value = to_char(l_new_element_type_id)
where key_value = to_char(r_distinct.c_surrogate_key)
and key_name = 'ELEMENT_TYPE_ID';
-- update the uid of associated input values
FOR i_vals IN inputs(r_distinct.c_surrogate_key) LOOP
BEGIN
-- Test if input value already exists
-- #331823. Add 'distinct' to prevent a 'too many rows'
-- error if there is more than one datetracked
-- version of the input values row.
select distinct input_value_id
into v_new_input_id
from pay_input_values_f
where replace(ltrim(rtrim(upper(name))), ' ', '_') =
replace(ltrim(rtrim(upper(i_vals.name))), ' ', '_')
and business_group_id is null
and element_type_id = l_new_element_Type_id;
select pay_input_values_s.nextval
into v_new_input_id
from dual;
update hr_s_input_values_f
set input_value_id = v_new_input_id
, element_type_id = l_new_element_type_id
, new_input_value_flag = v_new_input_value_flag
where input_value_id = i_vals.input_value_id;
update hr_s_balance_feeds_f
set input_value_id = v_new_input_id,
new_input_value_flag = v_new_input_value_flag
where input_value_id = i_vals.input_value_id;
update hr_s_formula_result_rules_f
set input_value_id = v_new_input_id
where input_value_id = i_vals.input_value_id;
update hr_s_balance_types
set input_value_id = v_new_input_id
where input_value_id = i_vals.input_value_id;
select pay_sub_classification_rules_s.nextval
into v_new_sub_class_id
from dual;
update hr_s_sub_classn_rules_f
set sub_classification_rule_id = v_new_sub_class_id
, element_type_id = l_new_element_type_id
where sub_classification_rule_id = s_class.sub_classification_rule_id;
select sub_classification_rule_id
into v_dummy
from hr_s_sub_classn_rules_f hscr
where hscr.sub_classification_rule_id = v_new_sub_class_id
and exists
( select 1
from pay_sub_classification_rules_f pscr
where pscr.element_type_id = hscr.element_type_id
and pscr.classification_id = hscr.classification_id
and nvl(pscr.business_group_id, -1) = nvl(hscr.business_group_id, -1)
and nvl(pscr.legislation_code, 'X') = nvl(hscr.legislation_code, 'X')
and pscr.effective_start_date = hscr.effective_start_date
and pscr.effective_end_date = hscr.effective_end_date);
update hr_s_sub_classn_rules_f
set new_sub_class_rule_flag = v_new_sub_class_rule_flag
where sub_classification_rule_id = s_class.sub_classification_rule_id;
select distinct status_processing_rule_id
into v_new_spr_id
from pay_status_processing_rules_f spr
where spr.legislation_code = r_distinct.legislation_code
and spr.business_group_id is null
and spr.processing_rule = sprs.processing_rule
and ((spr.assignment_status_type_id is null
and
sprs.assignment_status_type_id is null)
or
(spr.assignment_status_type_id =
sprs.assignment_status_type_id))
and spr.effective_end_date = (select max(spr2.effective_end_date)
from pay_status_processing_rules_f spr2
where spr2.element_type_id = spr.element_type_id
and spr2.processing_rule = spr.processing_rule
and spr2.legislation_code = r_distinct.legislation_code
and spr2.business_group_id is null
and ((spr.assignment_status_type_id is null
and spr2.assignment_status_type_id is null)
or
(spr.assignment_status_type_id =
spr2.assignment_status_type_id)))
and element_type_id = l_new_element_type_id;
select pay_status_processing_rules_s.nextval
into v_new_spr_id
from dual;
update hr_s_status_processing_rules_f
set status_processing_rule_id = v_new_spr_id
, element_type_id = l_new_element_type_id
where status_processing_rule_id = sprs.s_rule_id;
select pay_formula_Result_rules_s.nextval
into v_new_frr_id
from dual;
update hr_s_formula_result_rules_f
set formula_result_rule_id = v_new_frr_id
, status_processing_rule_id = v_new_spr_id
where formula_result_rule_id = results.formula_result_rule_id;
END update_uid;
select distinct null
into l_null_return
from pay_balance_feeds_f a
, pay_input_values_f b
where b.element_type_id = l_new_element_type_id
and a.input_value_id = b.input_value_id
and a.effective_end_Date > r_distinct.c_end
and a.business_group_id is not null;
select distinct null
into l_null_return
from pay_element_links_f
where element_type_id = l_new_element_type_id
and effective_end_Date > r_distinct.c_end
and business_group_id is not null;
select distinct null
into l_null_return
from pay_status_processing_rules_f a
, pay_formula_result_rules_f b
where a.element_type_id = l_new_element_type_id
and b.status_processing_rule_id = a.status_processing_rule_id
and b.effective_end_Date > r_distinct.c_end
and b.business_group_id is not null;
select input_value_id
into l_input_value_id
from pay_input_values_f
where element_type_id = l_new_element_type_id
and rownum = 1;
select 1
into l_null_return
from dual
where exists
(select /*+ ORDERED INDEX(a PAY_RUN_RESULTS_PK)
USE_NL(a b c) */ null
from pay_run_result_values v
, pay_run_results a
, pay_assignment_actions b
, pay_payroll_actions c
where v.input_value_id = l_input_value_id
and a.run_result_id = v.run_result_id
and b.assignment_action_id = a.assignment_action_id
and c.payroll_action_id = b.payroll_action_id
and c.effective_date > r_distinct.c_end);
select 1
into l_null_return
from dual
where exists
(select null
from pay_run_results a
, pay_assignment_actions b
, pay_payroll_actions c
where a.element_Type_id = l_new_element_type_id
and b.assignment_action_id = a.assignment_action_id
and c.payroll_action_id = b.payroll_action_id
and c.effective_date > r_distinct.c_end);
-- 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 null
into l_null_return
from hr_s_element_classifications
where classification_id = r_each_row.classification_id;
select null
into l_null_return
from pay_element_classifications
where classification_id = r_each_row.classification_id;
select distinct null
into l_null_return
from hr_s_formulas_f
where formula_id = r_each_row.formula_id;
select distinct null
into l_null_Return
from ff_formulas_f
where formula_id = r_each_row.formula_id;
select null
into l_null_return
from hr_s_benefit_classifications
where r_each_row.benefit_classification_id=benefit_classification_id;
select null
into l_null_return
from ben_benefit_classifications
where r_each_row.benefit_classification_id=benefit_classification_id;
select /*+ INDEX_FFS(pe) */ business_group_id
from pay_element_types_f pe
where business_group_id is not null
and replace(ltrim(rtrim(upper(element_name))), ' ', '_') =
replace(ltrim(rtrim(upper(r_distinct.c_true_key))), ' ', '_');
-- 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 pointless to perform this logic again.
-- An exception is raised 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
--
--if exception raised then this row is not needed
if (r_distinct.legislation_subgroup is null) then
select distinct 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 = 'ELEMENT_TYPE_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 distinct 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 = 'ELEMENT_TYPE_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')))
and exists
(select null
from hr_legislation_subgroups d
where d.legislation_code = r_distinct.legislation_code
and d.legislation_subgroup =
r_distinct.legislation_subgroup
and d.active_inactive_flag = 'A'
);
select distinct null
into l_null_return
from pay_element_types_f a
where a.business_group_id is not null
and replace(ltrim(rtrim(upper(a.element_name))), ' ', '_') =
replace(ltrim(rtrim(upper(r_distinct.c_true_key))), ' ', '_');
select distinct null
into l_null_return
from per_business_groups pbg
where pbg.business_group_id = elts.business_group_id
and pbg.legislation_code = r_distinct.legislation_code;
select distinct null
into l_null_return
from pay_element_types_f
where element_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 )
and business_group_id is null;
PROCEDURE delete_live_children
------------------------------
IS
-- Deletes rows from a live account in readiness for them to be installed
BEGIN
delete from pay_sub_classification_rules_f
where element_type_id = r_distinct.c_surrogate_key
and business_group_id is null;
delete from pay_formula_result_rules_f a
where a.business_group_id is null
and exists
(select null
from pay_status_processing_rules_f b
where b.status_processing_rule_id = a.status_processing_rule_id
and b.element_type_id = r_distinct.c_surrogate_key
and b.business_group_id is null
);
delete from pay_status_processing_rules_f
where element_type_id = r_distinct.c_surrogate_key
and business_Group_id is null;
delete from pay_element_types_f
where element_type_id = r_distinct.c_surrogate_key
and business_Group_id is null;
END delete_live_children;
delete from pay_input_values_f
where business_group_id is null
and input_value_id = i_values.input_value_id;
select FLEX_VALUE_SET_ID
into l_flex_value_set_id
from fnd_flex_value_sets
where FLEX_VALUE_SET_NAME = i_values.value_set_name;
insert into pay_input_values_f
(input_value_id
,effective_start_date
,effective_end_date
,element_type_id
,lookup_type
,business_group_id
,legislation_code
,formula_id
,display_sequence
,generate_db_items_flag
,hot_default_flag
,mandatory_flag
,name
,uom
,default_value
,legislation_subgroup
,max_value
,min_value
,warning_or_error
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,value_set_id
)
select input_value_id
,effective_start_date
,effective_end_date
,element_type_id
,lookup_type
,business_group_id
,legislation_code
,formula_id
,display_sequence
,generate_db_items_flag
,hot_default_flag
,mandatory_flag
,name
,uom
,default_value
,legislation_subgroup
,max_value
,min_value
,warning_or_error
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,l_flex_value_set_id
from hr_s_input_values_f
where input_value_id = i_values.input_value_id;
delete from hr_s_input_values_f
where input_value_id = i_values.input_value_id;
select distinct null
into l_null_Return
from hr_s_input_values_f
where element_type_id = l_new_element_type_id;
select distinct null
into l_null_return
from pay_element_types_f a
where a.business_Group_id is null
and a.element_name = p_element_name
and exists
(select null
from pay_element_types_f b
where b.element_type_id <> a.element_Type_id
and b.element_name = a.element_name
and b.business_Group_id is null
and b.legislation_code = a.legislation_code
and a.effective_start_date between b.effective_start_date and
b.effective_end_date
);
-- Function to insert date effective element rows for a given element type id
BEGIN
IF NOT valid_ownership THEN
return FALSE;
update_uid;
SELECT status
INTO l_payroll_install_status
FROM fnd_product_installations
WHERE application_id = 801;
delete_live_children;
insert into pay_element_types_f
(element_type_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,input_currency_code
,output_currency_code
,classification_id
,benefit_classification_iD
,additional_entry_allowed_flag
,adjustment_only_flag
,closed_for_entry_flag
,element_name
,indirect_only_flag
,multiply_value_flag
,post_termination_rule
,process_in_run_flag
,processing_priority
,processing_type
,standard_link_flag
,formula_id
,comment_id
,description
,legislation_subgroup
,qualifying_age
,qualifying_length_of_service
,qualifying_units
,reporting_name
,third_party_pay_only_flag
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,multiple_entries_allowed_flag
,element_information_category
,element_information1
,element_information2
,element_information3
,element_information4
,element_information5
,element_information6
,element_information7
,element_information8
,element_information9
,element_information10
,element_information11
,element_information12
,element_information13
,element_information14
,element_information15
,element_information16
,element_information17
,element_information18
,element_information19
,element_information20
,iterative_flag
,iterative_formula_id
,iterative_priority
,retro_summ_ele_id
,grossup_flag
,process_mode
,proration_group_id
,proration_formula_id
,TIME_DEFINITION_TYPE
,TIME_DEFINITION_ID
)
values
(each_row.element_type_id
,each_row.effective_start_date
,each_row.effective_end_date
,each_row.business_group_id
,each_row.legislation_code
,each_row.input_currency_code
,each_row.output_currency_code
,each_row.classification_id
,each_row.benefit_classification_iD
,each_row.additional_entry_allowed_flag
,each_row.adjustment_only_flag
,each_row.closed_for_entry_flag
,each_row.element_name
,each_row.indirect_only_flag
,each_row.multiply_value_flag
,each_row.post_termination_rule
,each_row.process_in_run_flag
,each_row.processing_priority
,each_row.processing_type
,each_row.standard_link_flag
,l_formula_id
,each_row.comment_id
,each_row.description
,each_row.legislation_subgroup
,each_row.qualifying_age
,each_row.qualifying_length_of_service
,each_row.qualifying_units
,each_row.reporting_name
,each_row.third_party_pay_only_flag
,each_row.last_update_date
,each_row.last_updated_by
,each_row.last_update_login
,each_row.created_by
,each_row.creation_date
,each_row.multiple_entries_allowed_flag
,each_row.element_information_category
,each_row.element_information1
,each_row.element_information2
,each_row.element_information3
,each_row.element_information4
,each_row.element_information5
,each_row.element_information6
,each_row.element_information7
,each_row.element_information8
,each_row.element_information9
,each_row.element_information10
,each_row.element_information11
,each_row.element_information12
,each_row.element_information13
,each_row.element_information14
,each_row.element_information15
,each_row.element_information16
,each_row.element_information17
,each_row.element_information18
,each_row.element_information19
,each_row.element_information20
,each_row.iterative_flag
,each_row.iterative_formula_id
,each_row.iterative_priority
,each_row.retro_summ_ele_id
,each_row.grossup_flag
,each_row.process_mode
,each_row.proration_group_id
,each_row.proration_formula_id
,each_row.TIME_DEFINITION_TYPE
,each_row.TIME_DEFINITION_ID
);
select null
into l_null_return
from pay_element_classifications
where classification_id = s_rules.classification_id;
insert into pay_sub_classification_rules_f
(sub_classification_rule_id
,effective_start_date
,effective_end_date
,element_type_id
,classification_id
,business_group_id
,legislation_code
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(s_rules.sub_classification_rule_id
,s_rules.effective_start_date
,s_rules.effective_end_date
,s_rules.element_type_id
,s_rules.classification_id
,s_rules.business_group_id
,s_rules.legislation_code
,s_rules.last_update_date
,s_rules.last_updated_by
,s_rules.last_update_login
,s_rules.created_by
,s_rules.creation_date
);
insert into pay_status_processing_rules_f
(STATUS_PROCESSING_RULE_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,ELEMENT_TYPE_ID
,ASSIGNMENT_STATUS_TYPE_ID
,FORMULA_ID
,PROCESSING_RULE
,COMMENT_ID
,LEGISLATION_SUBGROUP
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
)
values
(all_rules.STATUS_PROCESSING_RULE_ID
,all_rules.EFFECTIVE_START_DATE
,all_rules.EFFECTIVE_END_DATE
,all_rules.BUSINESS_GROUP_ID
,all_rules.LEGISLATION_CODE
,all_rules.ELEMENT_TYPE_ID
,all_rules.ASSIGNMENT_STATUS_TYPE_ID
,all_rules.FORMULA_ID
,all_rules.PROCESSING_RULE
,all_rules.COMMENT_ID
,all_rules.LEGISLATION_SUBGROUP
,all_rules.LAST_UPDATE_DATE
,all_rules.LAST_UPDATED_BY
,all_rules.LAST_UPDATE_LOGIN
,all_rules.CREATED_BY
,all_rules.CREATION_DATE
);
insert into pay_formula_result_rules_f
(FORMULA_RESULT_RULE_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,STATUS_PROCESSING_RULE_ID
,RESULT_NAME
,RESULT_RULE_TYPE
,LEGISLATION_SUBGROUP
,SEVERITY_LEVEL
,INPUT_VALUE_ID
,ELEMENT_TYPE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
)
values
(all_frrs.FORMULA_RESULT_RULE_ID
,all_frrs.EFFECTIVE_START_DATE
,all_frrs.EFFECTIVE_END_DATE
,all_frrs.BUSINESS_GROUP_ID
,all_frrs.LEGISLATION_CODE
,all_frrs.STATUS_PROCESSING_RULE_ID
,all_frrs.RESULT_NAME
,all_frrs.RESULT_RULE_TYPE
,all_frrs.LEGISLATION_SUBGROUP
,all_frrs.SEVERITY_LEVEL
,all_frrs.INPUT_VALUE_ID
,all_frrs.ELEMENT_TYPE_ID
,all_frrs.LAST_UPDATE_DATE
,all_frrs.LAST_UPDATED_BY
,all_frrs.LAST_UPDATE_LOGIN
,all_frrs.CREATED_BY
,all_frrs.CREATION_DATE
);
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select element_set_name c_true_key
, element_set_id c_surrogate_key
, legislation_code c_leg_code
, element_set_type
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, rowid
from hr_s_element_sets;
select *
from hr_s_element_type_rules
where element_set_id = ele_set_id;
select *
from hr_s_ele_classn_rules
where element_set_id = ele_set_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 pay_element_sets a
where exists
(select null
from hr_s_element_sets b
where a.element_set_id = b.element_set_id
);
--update all element_set_id's to remove conflict
update /*+NO_INDEX*/ hr_s_element_sets
set element_set_id = element_set_id - 50000000;
update /*+NO_INDEX*/ hr_s_element_type_rules
set element_set_id = element_set_id - 50000000;
update /*+NO_INDEX*/ hr_s_ele_classn_rules
set element_set_id = element_set_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'ELEMENT_SET_ID';
select min(element_set_id) - (count(*) *3)
, max(element_set_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_element_sets;
select pay_element_sets_s.nextval
into v_sequence_number
from dual;
hr_legislation.insert_hr_stu_exceptions('pay_element_sets'
, stu_rec.c_surrogate_key
, exception_type
, stu_rec.c_true_key);
PROCEDURE update_uid
--------------------
IS
-- Subprogram to update surrogate UID and all occurrences in child rows
BEGIN
BEGIN
select distinct element_set_id
into l_new_surrogate_key
from pay_element_sets
where element_set_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 pay_element_sets_s.nextval
into l_new_surrogate_key
from dual;
--update all child entities
update hr_s_element_sets
set element_set_id = l_new_surrogate_key
where element_set_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 = 'ELEMENT_SET_ID';
update hr_s_element_type_rules
set element_set_id = l_new_surrogate_key
where element_set_id = stu_rec.c_surrogate_key;
update hr_s_ele_classn_rules
set element_set_id = l_new_surrogate_key
where element_set_id = stu_rec.c_surrogate_key;
END update_uid;
delete from hr_s_element_type_rules
where element_set_id = l_new_surrogate_key;
delete from hr_s_ele_classn_rules
where element_set_id = l_new_surrogate_key;
delete from hr_s_element_sets
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.
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 = 'ELEMENT_SET_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')));
-- 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 pay_element_sets a
where a.element_set_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 pay_element_sets
where element_set_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 )
and business_group_id 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;
delete from pay_element_type_rules
where element_set_id = l_new_surrogate_key;
delete from pay_ele_classification_rules
where element_set_id = l_new_surrogate_key;
update pay_element_sets
set element_set_type = stu_rec.element_set_type
, 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 element_set_id = stu_rec.c_surrogate_key;
insert into pay_element_sets
(element_set_name
,element_set_id
,legislation_code
,element_set_type
,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.c_leg_code
,stu_rec.element_set_type
,stu_rec.last_update_date
,stu_rec.last_updated_by
,stu_rec.last_update_login
,stu_rec.created_by
,stu_rec.creation_date
);
select null
into l_null_return
from pay_element_types_f
where element_type_id = ele_types.element_type_id;
insert into pay_element_type_rules
(element_type_id
,element_set_id
,include_or_exclude
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
values
(ele_types.element_Type_id
,ele_types.element_Set_id
,ele_types.include_or_exclude
,ele_types.last_update_date
,ele_types.last_updated_by
,ele_types.last_update_login
,ele_types.created_by
,ele_types.creation_date);
select null
into l_null_return
from pay_element_classifications
where classification_id = ele_class.classification_id;
insert into pay_ele_classification_rules
(element_set_id
,classification_id
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
values
(ele_class.element_set_id
,ele_class.classification_id
,ele_class.last_update_date
,ele_class.last_updated_by
,ele_class.last_update_login
,ele_class.created_by
,ele_class.creation_date);
update_uid;
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select user_table_id c_surrogate_key
, business_group_id
, legislation_code c_leg_code
, range_or_match
, user_key_units
, user_table_name c_true_key
, legislation_subgroup c_leg_sgrp
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, rowid
, user_row_title
from hr_s_user_tables;
-- 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 pay_user_tables a
where exists
(select null
from hr_s_user_tables b
where a.user_table_id = b.user_table_id
);
--update all user_table_id's to remove conflict
update /*+NO_INDEX*/ hr_s_user_columns
set user_table_id = user_table_id - 50000000;
update /*+NO_INDEX*/ hr_s_user_rows_f
set user_table_id = user_table_id - 50000000;
update /*+NO_INDEX*/ hr_s_user_tables
set user_table_id = user_table_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'USER_TABLE_ID';
select min(user_table_id) - (count(*) *3)
, max(user_table_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_user_tables;
select pay_user_tables_s.nextval
into v_sequence_number
from dual;
hr_legislation.insert_hr_stu_exceptions('pay_user_tables'
, stu_rec.c_surrogate_key
, exception_type
, stu_rec.c_true_key);
PROCEDURE update_uid
--------------------
IS
-- Subprogram to update surrogate UID and all occurrences in child rows
BEGIN
BEGIN
select distinct user_table_id
into l_new_surrogate_key
from pay_user_tables
where user_table_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 pay_user_tables_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_user_tables
set user_table_id = l_new_surrogate_key
where user_table_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 = 'USER_TABLE_ID';
update hr_s_user_columns
set user_table_id = l_new_surrogate_key
where user_table_id = stu_rec.c_surrogate_key;
update hr_s_user_rows_f
set user_table_id = l_new_surrogate_key
where user_table_id = stu_rec.c_surrogate_key;
END update_uid;
delete from hr_s_user_tables
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.
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 = 'USER_TABLE_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')));
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 = 'USER_TABLE_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')))
and exists (select null from hr_legislation_subgroups d
where d.legislation_code = stu_rec.c_leg_code
and d.legislation_subgroup = stu_rec.c_leg_sgrp
and d.active_inactive_flag = 'A'
);
-- 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 pay_user_tables a
where a.user_table_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 pay_user_tables
where user_table_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 )
and business_group_id 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 pay_user_tables
set range_or_match = stu_rec.range_or_match
, user_key_units = stu_rec.user_key_units
, 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
, user_row_title = stu_rec.user_row_title
where user_table_id = stu_rec.c_surrogate_key;
insert into pay_user_tables
(user_table_id
,business_group_id
,legislation_code
,range_or_match
,user_key_units
,user_table_name
,legislation_subgroup
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,user_row_title
)
values
(stu_rec.c_surrogate_key
,stu_rec.business_group_id
,stu_rec.c_leg_code
,stu_rec.range_or_match
,stu_rec.user_key_units
,stu_rec.c_true_key
,stu_rec.c_leg_sgrp
,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.user_row_title
);
update_uid;
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
--
-- #271139 - note that the user column name is not the true key on
-- its own; it's only unique for the user table.
-- Must use the user table id in select criteria for the true key.
--
select user_column_id c_surrogate_key
, business_group_id
, legislation_code c_leg_code
, user_table_id
, formula_id
, user_column_name c_true_key
, legislation_subgroup c_leg_sgrp
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, rowid
from hr_s_user_columns;
-- 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 pay_user_columns a
where exists
(select null
from hr_s_user_columns b
where a.user_column_id = b.user_column_id
);
--update all user_column_id's to remove conflict
update /*+NO_INDEX*/ hr_s_user_columns
set user_column_id = user_column_id - 50000000;
update /*+NO_INDEX*/ hr_s_user_column_instances_f
set user_column_id = user_column_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'USER_COLUMN_ID';
select min(user_column_id) - (count(*) *3)
, max(user_column_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_user_columns;
select pay_user_columns_s.nextval
into v_sequence_number
from dual;
hr_legislation.insert_hr_stu_exceptions('pay_user_columns'
, stu_rec.c_surrogate_key
, exception_type
, stu_rec.c_true_key);
PROCEDURE update_uid
--------------------
IS
-- subprogram to update surrogate UID and all occurrences in child rows
BEGIN
BEGIN
--
-- #271139 - hitting a problem because the user column name is
-- not the true key on its own; it's only unique for the user table.
-- Add the user table id to the select criteria.
--
select distinct user_column_id
into l_new_surrogate_key
from pay_user_columns
where user_column_name = stu_rec.c_true_key
and user_table_id = stu_rec.user_table_id
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 pay_user_columns_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_user_columns
set user_column_id = l_new_surrogate_key
where user_column_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 = 'USER_COLUMN_ID';
update hr_s_user_column_instances_f
set user_column_id = l_new_surrogate_key
where user_column_id = stu_rec.c_surrogate_key;
END update_uid;
delete from hr_s_user_columns
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 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.
BEGIN
IF p_phase <> 1 THEN
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 = 'USER_COLUMN_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')));
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 = 'USER_COLUMN_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')))
and exists (select null from hr_legislation_subgroups d
where d.legislation_code = stu_rec.c_leg_code
and d.legislation_subgroup = stu_rec.c_leg_sgrp
and d.active_inactive_flag = 'A'
);
-- 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.
-- Start with checking the parent PAY_USER_TABLES
BEGIN
-- Check the tables in the delivery account
select distinct null
into l_null_return
from hr_s_user_tables
where user_table_id = stu_rec.user_table_id;
select null
into l_null_return
from pay_user_tables
where user_table_id = stu_rec.user_table_id;
select distinct null
into l_null_return
from hr_s_formulas_f
where formula_id = stu_rec.formula_id;
select 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
-- Add the user table id to the select criteria.
--
select distinct null
into l_null_return
from pay_user_columns a
where a.user_table_id = stu_rec.user_table_id
and a.user_column_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));
-- Add the user table id to the select criteria.
--
BEGIN
select distinct null
into l_null_return
from pay_user_columns
where user_column_name = stu_rec.c_true_key
and user_table_id = stu_rec.user_table_id
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 )
and business_group_id 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 pay_user_columns
set formula_id = stu_rec.formula_id
, 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 user_column_id = stu_rec.c_surrogate_key;
insert into pay_user_columns
(user_column_id
,business_group_id
,legislation_code
,user_table_id
,formula_id
,user_column_name
,legislation_subgroup
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(stu_rec.c_surrogate_key
,stu_rec.business_group_id
,stu_rec.c_leg_code
,stu_rec.user_table_id
,stu_rec.formula_id
,stu_rec.c_true_key
,stu_rec.c_leg_sgrp
,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;