[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- 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
, qualifying_event_id c_surrogate_key
, qualifying_event c_true_key
, legislation_code c_leg_code
, legislation_subgroup c_leg_sgrp
from hr_s_cobra_qfying_events_f
group by qualifying_event_id
, qualifying_event
, legislation_code
, legislation_subgroup;
-- 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_cobra_qfying_events_f
where qualifying_event_id = pc_qualifying_event_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 per_cobra_qfying_events_f a
where exists
(select null
from hr_s_cobra_qfying_events_f b
where a.qualifying_event_id = b.qualifying_event_id
);
--update all qualifying_event_id's to remove conflict
update hr_s_cobra_qfying_events_f
set qualifying_event_id = qualifying_event_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'QUALIFYING_EVENT_ID';
select min(qualifying_event_id) - (count(*) *3)
, max(qualifying_event_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_cobra_qfying_events_f;
select per_cobra_qfying_events_s.nextval
into v_sequence_number
from dual;
select per_cobra_qfying_events_s.nextval
into v_sequence_number
from dual;
hr_legislation.insert_hr_stu_exceptions('per_cobra_qfying_events_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_cobra_qfying_events_f
where qualifying_event_id = v_id;
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 qualifying_event_id
into l_new_cqe_id
from per_cobra_qfying_events_f
where qualifying_event = r_distinct.c_true_key
and business_Group_id is null
and legislation_code = r_distinct.c_leg_code;
select per_cobra_qfying_events_s.nextval
into l_new_cqe_id
from dual;
update hr_s_cobra_qfying_events_f
set qualifying_event_id = l_new_cqe_id
where qualifying_event_id = r_distinct.c_surrogate_key;
update hr_s_application_ownerships
set key_value = to_char(l_new_cqe_id)
where key_value = to_char(r_distinct.c_surrogate_key)
and key_name = 'QUALIFYING_EVENT_ID';
END update_uid;
select distinct null
into l_null_return
from per_cobra_qfying_events_f a
where a.business_group_id is not null
and a.qualifying_event = r_distinct.c_true_key
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.c_leg_code,b.legislation_code));
select distinct null
into l_null_return
from per_cobra_qfying_events_f
where qualifying_event = r_distinct.c_true_key
and legislation_code <> r_distinct.c_leg_code
and (legislation_code is null
or r_distinct.c_leg_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.
-- 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 = 'QUALIFYING_EVENT_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 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 = 'QUALIFYING_EVENT_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.c_leg_code
and d.legislation_subgroup = r_distinct.c_leg_sgrp
and d.active_inactive_flag = 'A' );
-- Get new surrogate id and update child references
update_uid;
delete from per_cobra_qfying_events_f
where qualifying_event_id = r_distinct.c_surrogate_key;
insert into per_cobra_qfying_events_f
(qualifying_event_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,elector
,event_coverage
,qualifying_event
,legislation_subgroup
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(r_each_row.qualifying_event_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.elector
,r_each_row.event_coverage
,r_each_row.qualifying_event
,r_each_row.legislation_subgroup
,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
CURSOR stu -- Selects all rows from startup entity
IS
select benefit_classification_name c_true_key
, rowid
, benefit_classification_id c_surrogate_key
, business_group_id
, legislation_code c_leg_code
, active_flag
, beneficiary_allowed_flag
, benefit_classification_type
, chargeable_flag
, cobra_flag
, contributions_used
, dependents_allowed_flag
, dflt_post_termination_rule
, dflt_processing_type
, ben_class_processing_rule
, comments
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
from hr_s_benefit_classifications;
-- 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 ben_benefit_classifications a
where exists
(select null
from hr_s_benefit_classifications b
where a.benefit_classification_id=b.benefit_classification_id
);
--update all benefit_classification_id's to remove conflict
update hr_s_benefit_classifications
set benefit_classification_id=benefit_classification_id-50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'BENEFIT_CLASSIFICATION_ID';
select min(benefit_classification_id) - (count(*) *3)
, max(benefit_classification_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_benefit_classifications;
select ben_benefit_classifications_s.nextval
into v_sequence_number
from dual;
select ben_benefit_classifications_s.nextval
into v_sequence_number
from dual;
hr_legislation.insert_hr_stu_exceptions('ben_benefit_classifications'
, 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 benefit_classification_id
into l_new_surrogate_key
from ben_benefit_classifications
where benefit_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 ben_benefit_classifications_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_benefit_classifications
set benefit_classification_id = l_new_surrogate_key
where benefit_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 = 'BENEFIT_CLASSIFICATION_ID';
update hr_s_element_types_f
set benefit_classification_id = l_new_surrogate_key
where benefit_classification_id = stu_rec.c_surrogate_key;
END update_uid;
delete from hr_s_benefit_classifications
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
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 = 'BENEFIT_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')));
-- 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 creeated 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 ben_benefit_classifications a
where a.business_group_id is not null
and a.benefit_classification_name = stu_rec.c_true_key
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 ben_benefit_classifications
where benefit_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 );
-- 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 ben_benefit_classifications
set benefit_classification_name = stu_rec.c_true_key
, business_group_id = stu_rec.business_group_id
, legislation_code = stu_rec.c_leg_code
, active_flag = stu_rec.active_flag
, beneficiary_allowed_flag = stu_rec.beneficiary_allowed_flag
, benefit_classification_type = stu_rec.benefit_classification_type
, chargeable_flag = stu_rec.chargeable_flag
, cobra_flag = stu_rec.cobra_flag
, contributions_used = stu_rec.contributions_used
, dependents_allowed_flag = stu_rec.dependents_allowed_flag
, dflt_post_termination_rule = stu_rec.dflt_post_termination_rule
, dflt_processing_type = stu_rec.dflt_processing_type
, ben_class_processing_rule = stu_rec.ben_class_processing_rule
, comments = stu_rec.comments
, 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 benefit_classification_id = stu_rec.c_surrogate_key;
insert into ben_benefit_classifications
( benefit_classification_name
, benefit_classification_id
, business_group_id
, legislation_code
, active_flag
, beneficiary_allowed_flag
, benefit_classification_type
, chargeable_flag
, cobra_flag
, contributions_used
, dependents_allowed_flag
, dflt_post_termination_rule
, dflt_processing_type
, ben_class_processing_rule
, comments
, 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.business_group_id
, stu_rec.c_leg_code
, stu_rec.active_flag
, stu_rec.beneficiary_allowed_flag
, stu_rec.benefit_classification_type
, stu_rec.chargeable_flag
, stu_rec.cobra_flag
, stu_rec.contributions_used
, stu_rec.dependents_allowed_flag
, stu_rec.dflt_post_termination_rule
, stu_rec.dflt_processing_type
, stu_rec.ben_class_processing_rule
, stu_rec.comments
, 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;
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select contact_type||coverage_type c_true_key
, rowid
, valid_dependent_type_id c_surrogate_key
, business_group_id
, legislation_code c_leg_code
, contact_type
, coverage_type
, maximum_number
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
from hr_s_valid_dependent_types;
hr_legislation.insert_hr_stu_exceptions('ben_valid_dependent_types'
, 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 valid_dependent_type_id
into l_new_surrogate_key
from ben_valid_dependent_types
where contact_type||coverage_type = 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 ben_valid_dependent_types_s.nextval
into l_new_surrogate_key
from dual;
--update all child entities
update hr_s_valid_dependent_types
set valid_dependent_type_id = l_new_surrogate_key
where valid_dependent_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 = 'VALID_DEPENDENT_TYPE_ID';
END update_uid;
delete from hr_s_valid_dependent_types
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
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 = 'VALID_DEPENDENT_TYPE_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
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 ben_valid_dependent_types a
where a.business_group_id is not null
and a.contact_type||a.coverage_type = stu_rec.c_true_key
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 ben_valid_dependent_types
where contact_type||coverage_type = 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 ben_valid_dependent_types
set business_group_id = stu_rec.business_group_id
, legislation_code = stu_rec.c_leg_code
, contact_type = stu_rec.contact_type
, coverage_type = stu_rec.coverage_type
, maximum_number = stu_rec.maximum_number
, 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 valid_dependent_type_id = stu_rec.c_surrogate_key;
insert into ben_valid_dependent_types
( valid_dependent_type_id
, business_group_id
, legislation_code
, contact_type
, coverage_type
, maximum_number
, 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.contact_type
, stu_rec.coverage_type
, stu_rec.maximum_number
, 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;