[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_hr_stu_exceptions (p_table_name varchar2,
p_surrogate_id number,
p_text varchar2,
p_true_key varchar2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
insert into HR_STU_EXCEPTIONS
(table_name
,surrogate_id
,exception_text
,true_key)
select
upper(p_table_name)
,p_surrogate_id
,p_text
,p_true_key
from dual
where not exists
(select null
from hr_stu_exceptions
where p_surrogate_id = surrogate_id
and table_name = upper(p_table_name));
END insert_hr_stu_exceptions;
select increment_by
from all_sequences
where sequence_name = p_seq_name
and sequence_owner = l_per_owner;
SELECT COUNT(*)
INTO l_seq_managed
FROM hr_dm_databases;
l_sql_stmt := 'SELECT ' || l_per_owner || '.' || p_seq_name || '.NEXTVAL FROM DUAL';
INSERT INTO hr_application_ownerships
(key_name
,product_name
,key_value)
SELECT distinct ao.key_name
,ao.product_name
,ao.key_value
FROM hr_s_application_ownerships ao
,pay_element_classifications pec
WHERE pec.legislation_code = 'ZZ'
AND ao.key_name = 'CLASSIFICATION_ID'
AND TO_NUMBER(ao.key_value) = pec.classification_id
AND NOT EXISTS (SELECT null
FROM hr_application_ownerships ao2
WHERE ao2.key_name = ao.key_name
AND ao2.product_name = ao.product_name
AND ao2.key_value = ao.key_value)
UNION ALL
SELECT distinct ao.key_name
,ao.product_name
,ao.key_value
FROM hr_s_application_ownerships ao
,pay_balance_types pbt
WHERE pbt.legislation_code = 'ZZ'
AND ao.key_name = 'BALANCE_TYPE_ID'
AND TO_NUMBER(ao.key_value) = pbt.balance_type_id
AND NOT EXISTS (SELECT null
FROM hr_application_ownerships ao2
WHERE ao2.key_name = ao.key_name
AND ao2.product_name = ao.product_name
AND ao2.key_value = ao.key_value)
UNION ALL
SELECT distinct ao.key_name
,ao.product_name
,ao.key_value
FROM hr_s_application_ownerships ao
,pay_balance_dimensions pbd
WHERE pbd.legislation_code ='ZZ'
AND ao.key_name = 'BALANCE_DIMENSION_ID'
AND TO_NUMBER(ao.key_value) = pbd.balance_dimension_id
AND NOT EXISTS (SELECT null
FROM hr_application_ownerships ao2
WHERE ao2.key_name = ao.key_name
AND ao2.product_name = ao.product_name
AND ao2.key_value = ao.key_value)
UNION ALL
SELECT distinct ao.key_name
,ao.product_name
,ao.key_value
FROM hr_s_application_ownerships ao
,pay_defined_balances pdb
WHERE pdb.legislation_code ='ZZ'
AND ao.key_name = 'DEFINED_BALANCE_ID'
AND TO_NUMBER(ao.key_value) = pdb.defined_balance_id
AND NOT EXISTS (SELECT null
FROM hr_application_ownerships ao2
WHERE ao2.key_name = ao.key_name
AND ao2.product_name = ao.product_name
AND ao2.key_value = ao.key_value)
UNION ALL
SELECT distinct ao.key_name
,ao.product_name
,ao.key_value
FROM hr_s_application_ownerships ao
,ff_routes fr
,pay_balance_dimensions pbd
WHERE pbd.legislation_code ='ZZ'
AND ao.key_name = 'ROUTE_ID'
AND TO_NUMBER(ao.key_value) = fr.route_id
AND fr.route_id = pbd.route_id
AND NOT EXISTS (SELECT null
FROM hr_application_ownerships ao2
WHERE ao2.key_name = ao.key_name
AND ao2.product_name = ao.product_name
AND ao2.key_value = ao.key_value);
l_inst_rowid rowid; -- rowid of the installed row to update
l_null_return varchar2(1); -- used for 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select user_status
, per_system_status
, pay_system_status
, default_flag
, rowid
, assignment_status_type_id c_surrogate_key
, last_update_date
, legislation_code c_leg_code
, null c_leg_sgrp
from hr_s_assignment_status_types;
stu_rec stu%ROWTYPE; -- Record for above SELECT
insert_hr_stu_exceptions('per_assignment_status_types'
, stu_rec.c_surrogate_key
, exception_type
, 'User: ' || stu_rec.user_status ||
' PER: ' || stu_rec.per_system_status ||
' PAY: ' || stu_rec.pay_system_status);
-- 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 count(*)
into cnt
from hr_s_assignment_status_types;
select distinct null
into l_null_return
from per_assignment_status_types a
where exists
(select null
from hr_s_assignment_status_types b
where a.assignment_status_type_id=b.assignment_status_type_id
);
--update all assignment_status_type_id's to remove conflict
update /*+NO_INDEX*/ hr_s_assignment_status_types
set assignment_status_type_id=assignment_status_type_id - 50000000;
update /*+NO_INDEX*/ hr_s_status_processing_rules_f
set assignment_status_type_id=assignment_status_type_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'ASSIGNMENT_STATUS_TYPE_ID';
select min(assignment_status_type_id) - (count(*) *3)
, max(assignment_status_type_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_assignment_status_types;
select max(assignment_status_type_id)
into v_max_live
from per_assignment_status_types;
select per_assignment_status_types_s.nextval
into v_sequence_number
from dual;
PROCEDURE update_uid
--------------------
IS
BEGIN
BEGIN
select distinct assignment_status_type_id
into l_new_surrogate_key
from per_assignment_status_types
where user_status = stu_rec.user_status
and per_system_status = stu_rec.per_system_status
and business_group_id is null
and ( (pay_system_status is null and stu_rec.pay_system_status is null)
or (pay_system_status = stu_rec.pay_system_status) )
and ( (legislation_code is null and stu_rec.c_leg_code is null)
or (legislation_code = stu_rec.c_leg_code) );
select per_assignment_status_types_s.nextval
into l_new_surrogate_key
from dual;
update hr_s_assignment_status_types
set assignment_status_type_id = l_new_surrogate_key
where assignment_status_type_id = stu_rec.c_surrogate_key;
update hr_s_status_processing_rules_f
set assignment_status_type_id = l_new_surrogate_key
where assignment_status_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 = 'ASSIGNMENT_STATUS_TYPE_ID';
END update_uid;
delete from hr_s_assignment_status_types
where rowid = stu_rec.rowid;
delete from per_assignment_status_types
where rowid = l_inst_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.
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 = 'ASSIGNMENT_STATUS_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')));
-- Check if a delivered row is needed and insert into the
-- live tables if it is.
l_inst_row number(1);
select distinct 1
into l_inst_row
from per_assignment_status_types
where user_status = stu_rec.user_status
and per_system_status = stu_Rec.per_system_status
and business_group_id is null
and ( (pay_system_status is null and stu_rec.pay_system_status is null)
or (pay_system_status = stu_rec.pay_system_status) )
and ( (legislation_code is null and stu_rec.c_leg_code is null)
or (legislation_code = stu_rec.c_leg_code) );
select null
into l_null_return
from dual
where exists (
select null
from per_assignment_status_types a
, per_business_groups b
where a.default_flag = 'Y'
and a.per_system_status = stu_rec.per_system_status
and ( (a.business_group_id is not null
and b.business_group_id = a.business_group_id
and b.legislation_code =
nvl(stu_rec.c_leg_code,b.legislation_code) )
or (a.business_group_id is null
and nvl(a.legislation_code,'X') =
nvl(stu_rec.c_leg_code,'X') ) ));
-- 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.
-- The local variable 'l_inst_rowid' is used to decide if there is
-- a live row present or not. If this variable is not null it will
-- contain the rowid of the installed row to be updated.
-- The last step of the transfer, in phase 2, is to delete the now
-- transfered row from the delivery tables.
IF l_inst_row is null THEN
insert into per_assignment_status_types
(ASSIGNMENT_STATUS_TYPE_ID
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,ACTIVE_FLAG
,DEFAULT_FLAG
,PRIMARY_FLAG
,USER_STATUS
,PAY_SYSTEM_STATUS
,PER_SYSTEM_STATUS
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE)
select ASSIGNMENT_STATUS_TYPE_ID
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,ACTIVE_FLAG
,DEFAULT_FLAG
,PRIMARY_FLAG
,USER_STATUS
,PAY_SYSTEM_STATUS
,PER_SYSTEM_STATUS
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
from hr_s_assignment_status_types
where rowid = stu_rec.rowid;
-- Delete delivered row now it has been installed
remove('D');
IF p_phase = 1 THEN update_uid; END IF;
select max(effective_end_date) c_end
, BALANCE_CATEGORY_ID c_surrogate_key
, CATEGORY_NAME c_true_key
, legislation_code
from hr_s_balance_categories_f
group by BALANCE_CATEGORY_ID
, CATEGORY_NAME
, legislation_code;
select *
from hr_s_balance_categories_f
where BALANCE_CATEGORY_ID = pc_bal_cat_id;
select count(*)
into cnt
from hr_s_balance_categories_f;
select distinct null
into l_null_return
from pay_balance_categories_f a
where exists
(select null
from hr_s_balance_categories_f b
where a.BALANCE_CATEGORY_ID = b.BALANCE_CATEGORY_ID
);
update /*+NO_INDEX*/ hr_s_balance_categories_f
set BALANCE_CATEGORY_ID = BALANCE_CATEGORY_ID - 50000000;
update /*+NO_INDEX*/ hr_s_balance_types
set BALANCE_CATEGORY_ID = BALANCE_CATEGORY_ID - 50000000;
select min(BALANCE_CATEGORY_ID) - (count(*) *3)
, max(BALANCE_CATEGORY_ID) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_balance_categories_f;
select max(BALANCE_CATEGORY_ID)
into v_max_live
from pay_balance_categories_f;
select pay_balance_categories_s.nextval
into v_sequence_number
from dual;
insert_hr_stu_exceptions('pay_balance_categories_f'
, r_distinct.c_surrogate_key
, exception_type
, r_distinct.c_true_key);
delete from hr_s_balance_categories_f
where BALANCE_CATEGORY_ID = v_id;
PROCEDURE update_uid
--------------------
IS
BEGIN
BEGIN
select distinct BALANCE_CATEGORY_ID
into l_new_balance_category_id
from pay_balance_categories_f
where category_name = r_distinct.c_true_key
and business_Group_id is null
and nvl(legislation_code, 'x') = nvl(r_distinct.legislation_code,'x');
select pay_balance_categories_s.nextval
into l_new_balance_category_id
from dual;
update hr_s_balance_categories_f
set balance_category_id = l_new_balance_category_id
where balance_category_id = r_distinct.c_surrogate_key;
update hr_s_balance_types
set balance_category_id = l_new_balance_category_id
where balance_category_id = r_distinct.c_surrogate_key;
END update_uid;
select distinct null
into l_null_return
from pay_balance_categories_f a
where a.category_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
into l_null_return
from pay_balance_categories_f
where category_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);
update_uid;
delete from pay_balance_categories_f
where balance_category_id = r_distinct.c_surrogate_key;
insert into pay_balance_categories_f
( BALANCE_CATEGORY_ID
,CATEGORY_NAME
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,LEGISLATION_CODE
,BUSINESS_GROUP_ID
,SAVE_RUN_BALANCE_ENABLED
,PBC_INFORMATION_CATEGORY
,PBC_INFORMATION1
,PBC_INFORMATION2
,PBC_INFORMATION3
,PBC_INFORMATION4
,PBC_INFORMATION5
,PBC_INFORMATION6
,PBC_INFORMATION7
,PBC_INFORMATION8
,PBC_INFORMATION9
,PBC_INFORMATION10
,PBC_INFORMATION11
,PBC_INFORMATION12
,PBC_INFORMATION13
,PBC_INFORMATION14
,PBC_INFORMATION15
,PBC_INFORMATION16
,PBC_INFORMATION17
,PBC_INFORMATION18
,PBC_INFORMATION19
,PBC_INFORMATION20
,PBC_INFORMATION21
,PBC_INFORMATION22
,PBC_INFORMATION23
,PBC_INFORMATION24
,PBC_INFORMATION25
,PBC_INFORMATION26
,PBC_INFORMATION27
,PBC_INFORMATION28
,PBC_INFORMATION29
,PBC_INFORMATION30
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,OBJECT_VERSION_NUMBER
,USER_CATEGORY_NAME)
values
(each_row.BALANCE_CATEGORY_ID
,each_row.CATEGORY_NAME
,each_row.EFFECTIVE_START_DATE
,each_row.EFFECTIVE_END_DATE
,each_row.LEGISLATION_CODE
,each_row.BUSINESS_GROUP_ID
,each_row.SAVE_RUN_BALANCE_ENABLED
,each_row.PBC_INFORMATION_CATEGORY
,each_row.PBC_INFORMATION1
,each_row.PBC_INFORMATION2
,each_row.PBC_INFORMATION3
,each_row.PBC_INFORMATION4
,each_row.PBC_INFORMATION5
,each_row.PBC_INFORMATION6
,each_row.PBC_INFORMATION7
,each_row.PBC_INFORMATION8
,each_row.PBC_INFORMATION9
,each_row.PBC_INFORMATION10
,each_row.PBC_INFORMATION11
,each_row.PBC_INFORMATION12
,each_row.PBC_INFORMATION13
,each_row.PBC_INFORMATION14
,each_row.PBC_INFORMATION15
,each_row.PBC_INFORMATION16
,each_row.PBC_INFORMATION17
,each_row.PBC_INFORMATION18
,each_row.PBC_INFORMATION19
,each_row.PBC_INFORMATION20
,each_row.PBC_INFORMATION21
,each_row.PBC_INFORMATION22
,each_row.PBC_INFORMATION23
,each_row.PBC_INFORMATION24
,each_row.PBC_INFORMATION25
,each_row.PBC_INFORMATION26
,each_row.PBC_INFORMATION27
,each_row.PBC_INFORMATION28
,each_row.PBC_INFORMATION29
,each_row.PBC_INFORMATION30
,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.OBJECT_VERSION_NUMBER
,nvl(each_row.USER_CATEGORY_NAME,
each_row.CATEGORY_NAME));
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select balance_name c_true_key
, balance_type_id c_surrogate_key
, legislation_code c_leg_code
, legislation_subgroup c_leg_sgrp
, assignment_remuneration_flag
, currency_code
, balance_uom
, reporting_name
, jurisdiction_level
, tax_type
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, input_value_id
, base_balance_type_id
, balance_category_id
, rowid
, new_balance_type_flag
from hr_s_balance_types;
select distinct *
from hr_s_balance_classifications hsbc
where balance_type_id = bal_type_id
and not exists
( select 1
from pay_balance_classifications pbc
where nvl(hsbc.business_group_id, -1) = nvl(pbc.business_group_id, -1)
and hsbc.legislation_code = pbc.legislation_code
and hsbc.balance_type_id = pbc.balance_type_id
and hsbc.classification_id = pbc.classification_id
and hsbc.scale = pbc.scale
and nvl(hsbc.legislation_subgroup, 'X') = nvl(pbc.legislation_subgroup, 'X'));
select distinct *
from hr_s_defined_balances
where balance_type_id = bal_type_id;
select distinct *
from hr_s_balance_feeds_f hrs
where hrs.balance_type_id = bal_type_id
and not exists (
select null
from pay_balance_feeds_f pbf
where pbf.balance_type_id = hrs.balance_type_id
and pbf.input_value_id = hrs.input_value_id
and pbf.effective_start_date = hrs.effective_start_date
and pbf.effective_end_date = hrs.effective_end_date);
stu_rec stu%ROWTYPE; -- Cursor for earlier select
-- 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 count(*)
into cnt
from hr_s_balance_types;
select distinct null
into l_null_return
from pay_balance_types a
where exists
(select null
from hr_s_balance_types b
where a.balance_type_id = b.balance_type_id
);
--update all balance_type_id's to remove conflict
update /*+NO_INDEX*/ hr_s_BALANCE_CLASSIFICATIONS
set balance_type_id = balance_type_id - 50000000;
update /*+NO_INDEX*/ hr_s_BALANCE_FEEDS_F
set balance_type_id = balance_type_id - 50000000;
update /*+NO_INDEX*/ hr_s_BALANCE_TYPES
set balance_type_id = balance_type_id - 50000000;
update /*+NO_INDEX*/ hr_s_BALANCE_TYPES
set base_balance_type_id = base_balance_type_id - 50000000;
update /*+NO_INDEX*/ hr_s_DEFINED_BALANCES
set balance_type_id = balance_type_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'BALANCE_TYPE_ID';
select count(*)
into cnt
from hr_s_defined_balances;
select distinct null
into l_null_return
from pay_defined_balances a
where exists
(select null
from hr_s_defined_balances b
where a.defined_balance_id = b.defined_balance_id
);
--update all balance_type_id's to remove conflict
update /*+NO_INDEX*/ hr_s_DEFINED_BALANCES
set defined_balance_id = defined_balance_id - 50000000;
select min(balance_type_id) - (count(*) *3)
, max(balance_type_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_balance_types;
select max(balance_type_id)
into v_max_live
from pay_balance_types;
select pay_balance_types_s.nextval
into v_sequence_number
from dual;
select min(defined_balance_id) - (count(*) *3)
, max(defined_balance_id) +(count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_defined_balances;
select pay_defined_balances_s.nextval
into v_sequence_number
from dual;
insert_hr_stu_exceptions('pay_balance_types'
, stu_rec.c_surrogate_key
, exception_type
, stu_rec.c_true_key);
PROCEDURE update_uid
--------------------
IS
v_new_def_bal_id number(15);
select distinct balance_type_id
into l_new_surrogate_key
from pay_balance_types
where replace(ltrim(rtrim(upper(balance_name))), ' ', '_') =
replace(ltrim(rtrim(upper(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_balance_types_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_balance_types
set balance_type_id = l_new_surrogate_key
where balance_type_id = stu_rec.c_surrogate_key;
update hr_s_balance_types
set base_balance_type_id = l_new_surrogate_key,
new_balance_type_flag = v_new_balance_type_flag
where base_balance_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 = 'BALANCE_TYPE_ID';
update hr_s_balance_classifications
set balance_type_id = l_new_surrogate_key
where balance_type_id = stu_rec.c_surrogate_key;
update hr_s_balance_feeds_f
set balance_type_id = l_new_surrogate_key,
new_balance_type_flag = v_new_balance_type_flag
where balance_type_id = stu_rec.c_surrogate_key;
-- Select the currently installed defined balance id, using
-- the by now updated balance_dimension_id and the new balance_type_id.
-- The balance_type_id will find its way onto the defined_balance row
-- when the row is updated with a new surrogate key.
FOR def_bals IN defined(stu_rec.c_surrogate_key) LOOP
BEGIN
select defined_balance_id
into v_new_def_bal_id
from pay_defined_balances
where balance_type_id = l_new_surrogate_key
and balance_dimension_id = def_bals.balance_dimension_id
and business_group_id is null
and ( (legislation_code is null and def_bals.legislation_code is null)
or (legislation_code = def_bals.legislation_code) );
select pay_defined_balances_s.nextval
into v_new_def_bal_id
from dual;
update hr_s_defined_balances
set defined_balance_id = v_new_def_bal_id
, balance_type_id = l_new_surrogate_key
where defined_balance_id = def_bals.defined_balance_id
and balance_type_id = def_bals.balance_type_id;
END update_uid;
delete from hr_s_balance_classifications
where balance_type_id = stu_Rec.c_surrogate_key;
delete from hr_s_defined_balances
where balance_type_id = stu_Rec.c_surrogate_key;
delete from hr_s_balance_feeds_f
where balance_type_id = stu_Rec.c_surrogate_key;
delete from hr_s_balance_types
where rowid = stu_rec.rowid;
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 = 'BALANCE_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')));
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 = 'BALANCE_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')))
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' );
select /*+ INDEX_FFS(pb) */ business_group_id
from pay_balance_types pb
where business_group_id is not null
and replace(ltrim(rtrim(upper(balance_name))), ' ', '_') =
replace(ltrim(rtrim(upper(stu_rec.c_true_key))), ' ', '_');
select status
into v_payroll_install_status
from fnd_product_installations
where application_id = 801;
select distinct null
into l_null_return
from pay_balance_types a
where a.business_group_id is not null
and replace(ltrim(rtrim(upper(a.balance_name))), ' ', '_') =
replace(ltrim(rtrim(upper(stu_rec.c_true_key))), ' ', '_');
select distinct null
into l_null_return
from per_business_groups pbg
where pbg.business_group_id = bals.business_group_id
and pbg.legislation_code = stu_rec.c_leg_code;
select distinct null
into l_null_return
from pay_balance_types
where balance_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;
-- identical the delivered row will be deleted.
-- The child rows will be deleted in the 'remove' function.
-- If the balance type is to be installed, check the child
-- defined balances. These children should be removed from the delivery
-- tables if they match the installed rows. This check is performed in
-- the exception handler, since this is where the installation of the
-- balance type is first identified.
--
-- #331831. Add NVLs wherever values may be null, to prevent flagging
-- rows which are identical but have some null values as different.
--
-- See comments in transfer_row procedure within install_past
-- procedure.
IF p_phase = 1 THEN return; END IF;
select distinct null
into l_null_return
from pay_balance_categories_f
where balance_category_id = stu_rec.balance_category_id;
update pay_balance_types
set business_group_id = null
, legislation_code = stu_rec.c_leg_code
, legislation_subgroup = stu_rec.c_leg_sgrp
, assignment_remuneration_flag=stu_rec.assignment_remuneration_flag
, currency_code = stu_rec.currency_code
, balance_uom = stu_rec.balance_uom
, reporting_name = stu_rec.reporting_name
, jurisdiction_level = stu_rec.jurisdiction_level
, tax_type = stu_rec.tax_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
, input_value_id = stu_rec.input_value_id
, base_balance_type_id = stu_rec.base_balance_type_id
, balance_category_id = stu_rec.balance_category_id
where balance_type_id = stu_rec.c_surrogate_key;
-- No row there to update, must insert
BEGIN
insert into pay_balance_types
(balance_name
,balance_type_id
,legislation_code
,legislation_subgroup
,assignment_remuneration_flag
,currency_code
,balance_uom
,reporting_name
,jurisdiction_level
,tax_type
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,input_value_id
,base_balance_type_id
,balance_category_id )
values
(stu_rec.c_true_key
,stu_rec.c_surrogate_key
,stu_rec.c_leg_code
,stu_rec.c_leg_sgrp
,stu_rec.assignment_remuneration_flag
,stu_rec.currency_code
,stu_rec.balance_uom
,stu_rec.reporting_name
,stu_rec.jurisdiction_level
,stu_rec.tax_type
,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.input_value_id
,stu_rec.base_balance_type_id
,stu_rec.balance_category_id);
-- At this stage the balance type is either installed new or updated.
-- Therefore all balance classifications will be refreshed. The first
-- stage is to delete those already there, then insert all
-- classification rows in the delivery tables.
--
-- store balance feed currval for latest use - so don't delete any of the
-- classification feeds created (if don't exist in hr_s)
select pay_balance_feeds_s.nextval
into l_initbfid
from dual;
delete from pay_balance_classifications pbc
where balance_type_id = stu_rec.c_surrogate_key
and not exists
( select 1
from hr_s_balance_classifications hsbc
where nvl(hsbc.business_group_id, -1) = nvl(pbc.business_group_id, -1)
and hsbc.legislation_code = pbc.legislation_code
and hsbc.balance_type_id = pbc.balance_type_id
and hsbc.classification_id = pbc.classification_id
and hsbc.scale = pbc.scale
and nvl(hsbc.legislation_subgroup, 'X') = nvl(pbc.legislation_subgroup, 'X'));
-- If the select raises an exception then the classification does not
-- exist. Otherwise the row will be inserted.
FOR bal_classes IN class(stu_rec.c_surrogate_key) LOOP
BEGIN
select distinct null
into l_null_return
from pay_element_classifications
where classification_id = bal_classes.classification_id;
insert into pay_balance_classifications
(BALANCE_CLASSIFICATION_ID
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,BALANCE_TYPE_ID
,CLASSIFICATION_ID
,SCALE
,LEGISLATION_SUBGROUP
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE)
select pay_balance_classifications_s.nextval
, bal_classes.business_group_id
, bal_classes.legislation_code
, bal_classes.balance_type_id
, bal_classes.classification_id
, bal_classes.scale
, bal_classes.legislation_subgroup
, bal_classes.last_update_date
, bal_classes.last_updated_by
, bal_classes.last_update_login
, bal_classes.created_by
, bal_classes.creation_date
from dual;
select pay_balance_classifications_s.currval
into l_bal_class_id
from dual;
-- the select raises an exception then the dimension does not exist.
-- Otherwise the row will be inserted. At this stage the
-- defined_balance_id will not exist in the live tables. Consequently
-- only new defined_balances will remain in the delivery tables.
FOR def_bals IN defined(stu_rec.c_surrogate_key) LOOP
BEGIN
select distinct null
into l_null_return
from pay_balance_dimensions
where balance_dimension_id = def_bals.balance_dimension_id;
update pay_defined_balances
set BUSINESS_GROUP_ID = null,
LEGISLATION_CODE = def_bals.LEGISLATION_CODE,
BALANCE_TYPE_ID = def_bals.BALANCE_TYPE_ID,
BALANCE_DIMENSION_ID = def_bals.BALANCE_DIMENSION_ID,
FORCE_LATEST_BALANCE_FLAG = def_bals.FORCE_LATEST_BALANCE_FLAG,
LEGISLATION_SUBGROUP = def_bals.LEGISLATION_SUBGROUP,
LAST_UPDATE_DATE = def_bals.LAST_UPDATE_DATE,
LAST_UPDATED_BY = def_bals.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = def_bals.LAST_UPDATE_LOGIN,
CREATED_BY = def_bals.CREATED_BY,
CREATION_DATE = def_bals.CREATION_DATE,
GROSSUP_ALLOWED_FLAG = def_bals.GROSSUP_ALLOWED_FLAG,
SAVE_RUN_BALANCE = def_bals.SAVE_RUN_BALANCE
-- RUN_BALANCE_STATUS = def_bals.RUN_BALANCE_STATUS
where DEFINED_BALANCE_ID = def_bals.defined_balance_id;
insert into pay_defined_balances
(DEFINED_BALANCE_ID
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,BALANCE_TYPE_ID
,BALANCE_DIMENSION_ID
,FORCE_LATEST_BALANCE_FLAG
,LEGISLATION_SUBGROUP
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,GROSSUP_ALLOWED_FLAG
,SAVE_RUN_BALANCE
,RUN_BALANCE_STATUS)
values
(def_bals.defined_balance_id
,null
,def_bals.legislation_code
,def_bals.balance_type_id
,def_bals.balance_dimension_id
,def_bals.force_latest_balance_flag
,def_bals.legislation_subgroup
,def_bals.last_update_date
,def_bals.last_updated_by
,def_bals.last_update_login
,def_bals.created_by
,def_bals.creation_date
,def_bals.grossup_allowed_flag
,def_bals.save_run_balance
,def_bals.run_balance_status);
delete from pay_balance_feeds_f pbf
where pbf.balance_type_id = stu_rec.c_surrogate_key
and pbf.business_group_id is null
and pbf.legislation_code is not null
and pbf.balance_feed_id <= l_initbfid
and not exists (
select null
from hr_s_balance_feeds_f hrs
where pbf.balance_type_id = hrs.balance_type_id
and pbf.input_value_id = hrs.input_value_id
and pbf.effective_start_date = hrs.effective_start_date
and pbf.effective_end_date = hrs.effective_end_date);
-- the select raises an exception then the input value does not exist.
-- Otherwise the row will be inserted.
FOR bal_feeds IN feed(stu_rec.c_surrogate_key) LOOP
BEGIN
select distinct null
into l_null_return
from pay_input_values_f
where input_value_id = bal_feeds.input_value_id;
insert into pay_balance_feeds_f
(balance_feed_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,balance_type_id
,input_value_id
,scale
,legislation_subgroup
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
select pay_balance_feeds_s.nextval
,bal_feeds.effective_start_date
,bal_feeds.effective_end_date
,bal_feeds.business_group_id
,bal_feeds.legislation_code
,bal_feeds.balance_type_id
,bal_feeds.input_value_id
,bal_feeds.scale
,bal_feeds.legislation_subgroup
,bal_feeds.last_update_date
,bal_feeds.last_updated_by
,bal_feeds.last_update_login
,bal_feeds.created_by
,bal_feeds.creation_date
from dual;
IF p_phase = 1 THEN update_uid; END IF;
l_null_return varchar2(1); -- used for 'select null' statements
CURSOR stu -- selects all rows from startup entity
IS
select dimension_name c_true_key
, balance_dimension_id c_surrogate_key
, route_id
, legislation_code c_leg_code
, legislation_subgroup c_leg_sgrp
, database_item_suffix
, dimension_type
, description
, feed_checking_code
, feed_checking_type
, payments_flag
, expiry_checking_code
, expiry_checking_level
, dimension_level
, period_type
, asg_action_balance_dim_id
, database_item_function
, save_run_balance_enabled
, start_date_code
, rowid
from hr_s_balance_dimensions;
stu_rec stu%ROWTYPE; -- Record for the above select
-- 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 count(*)
into cnt
from hr_s_balance_dimensions;
select distinct null
into l_null_return
from pay_balance_dimensions a
where exists
(select null
from hr_s_balance_dimensions b
where a.balance_dimension_id = b.balance_dimension_id
);
--update all balance_dimension_id's to remove conflict
update /*+NO_INDEX*/ hr_s_balance_dimensions
set balance_dimension_id = balance_dimension_id - 50000000;
update /*+NO_INDEX*/ hr_s_defined_balances
set balance_dimension_id = balance_dimension_id - 50000000;
update /*+NO_INDEX*/ hr_s_balance_dimensions
set asg_action_balance_dim_id = asg_action_balance_dim_id
- 50000000;
update /*+NO_INDEX*/ hr_s_dimension_routes
set balance_dimension_id = balance_dimension_id - 50000000;
update /*+NO_INDEX*/ hr_s_dimension_routes
set run_dimension_id = run_dimension_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'BALANCE_DIMENSION_ID';
select min(balance_dimension_id) - (count(*) *3)
, max(balance_dimension_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_balance_dimensions;
select max(balance_dimension_id)
into v_max_live
from pay_balance_dimensions;
select pay_balance_dimensions_s.nextval
into v_sequence_number
from dual;
insert_hr_stu_exceptions('pay_balance_dimensions'
, 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 balance_dimension_id
into l_new_surrogate_key
from pay_balance_dimensions
where dimension_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_balance_dimensions_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_balance_dimensions
set balance_dimension_id = l_new_surrogate_key
where balance_dimension_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 = 'BALANCE_DIMENSION_ID';
update hr_s_defined_balances
set balance_dimension_id = l_new_surrogate_key
where balance_dimension_id = stu_rec.c_surrogate_key;
update hr_s_dimension_routes
set balance_dimension_id = l_new_surrogate_key
where balance_dimension_id = stu_rec.c_surrogate_key;
update hr_s_dimension_routes
set run_dimension_id = l_new_surrogate_key
where run_dimension_id = stu_rec.c_surrogate_key;
update hr_s_balance_dimensions
set asg_action_balance_dim_id = l_new_surrogate_key
where asg_action_balance_dim_id = stu_rec.c_surrogate_key;
END update_uid;
delete from hr_s_balance_dimensions
where rowid = stu_rec.rowid;
l_null_return varchar2(1); -- used for 'select null' statements
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 = 'BALANCE_DIMENSION_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 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 = 'BALANCE_DIMENSION_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.
BEGIN
-- Start the checking against the first parent table
select distinct null
into l_null_return
from hr_s_routes
where route_id = stu_rec.route_id;
select null
into l_null_return
from ff_routes
where route_id = stu_rec.route_id;
v_inst_update date; -- Hold update details of installed row
select distinct null
from pay_balance_dimensions a
where a.dimension_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
from pay_balance_dimensions
where dimension_name = stu_rec.c_true_key
and legislation_code <> stu_rec.c_leg_code
and (legislation_code is null or stu_rec.c_leg_code is null);
-- identical the delivered row will be deleted.
delete from hr_s_balance_dimensions a
where a.balance_dimension_id = stu_rec.c_surrogate_key
and exists (
select 1 from pay_balance_dimensions b
where a.ROUTE_ID = b.route_id
and a.DATABASE_ITEM_SUFFIX = b.DATABASE_ITEM_SUFFIX
and a.DIMENSION_TYPE = b.DIMENSION_TYPE
and length(a.FEED_CHECKING_CODE) = length(b.FEED_CHECKING_CODE)
and a.FEED_CHECKING_TYPE = b.FEED_CHECKING_TYPE
and a.PAYMENTS_FLAG = b.PAYMENTS_FLAG
and length(a.EXPIRY_CHECKING_CODE) = length(b.EXPIRY_CHECKING_CODE)
and a.EXPIRY_CHECKING_LEVEL = b.EXPIRY_CHECKING_LEVEL
and a.DIMENSION_LEVEL = b.DIMENSION_LEVEL
and a.PERIOD_TYPE = b.PERIOD_TYPE);
-- 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.
IF NOT check_parents THEN return; END IF;
-- 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
-- transferred row from the delivery tables.
-- Delete the user entity for a dimension if its
-- about to have its route_id changed. Bug 4328538.
BEGIN
select route_id
into l_route_id
from pay_balance_dimensions
where balance_dimension_id = stu_rec.c_surrogate_key;
delete ff_compiled_info_f
where formula_id in (
select fdi.formula_id
from ff_fdi_usages_f fdi,
ff_user_entities ue,
pay_defined_balances db,
pay_balance_dimensions bd,
ff_database_items di
where fdi.item_name = di.user_name
and ue.creator_type = 'B'
and ue.creator_id = db.defined_balance_id
and bd.balance_dimension_id = db.balance_dimension_id
and bd.balance_dimension_id = stu_rec.c_surrogate_key
and di.user_entity_id = ue.user_entity_id);
delete from ff_fdi_usages_f fdi2
where fdi2.formula_id in
(select fdi.formula_id
from ff_fdi_usages_f fdi,
ff_user_entities ue,
pay_defined_balances db,
pay_balance_dimensions bd,
ff_database_items di
where fdi.item_name = di.user_name
and ue.creator_type = 'B'
and ue.creator_id = db.defined_balance_id
and bd.balance_dimension_id = db.balance_dimension_id
and bd.balance_dimension_id = stu_rec.c_surrogate_key
and di.user_entity_id = ue.user_entity_id);
delete from ff_user_entities
where creator_type = 'B'
and creator_id in
(select defined_balance_id
from pay_defined_balances pdb
where pdb.balance_dimension_id = stu_rec.c_surrogate_key);
update pay_balance_dimensions
set route_id = stu_rec.route_id
, database_item_suffix = stu_rec.database_item_suffix
, dimension_type = stu_rec.dimension_type
, description = stu_rec.description
, feed_checking_code = stu_rec.feed_checking_code
, feed_checking_type = stu_rec.feed_checking_type
, payments_flag = stu_rec.payments_flag
, expiry_checking_code = stu_rec.expiry_checking_code
, expiry_checking_level = stu_rec.expiry_checking_level
, dimension_level = stu_rec.dimension_level
, period_type = stu_rec.period_type
, asg_action_balance_dim_id = stu_rec.asg_action_balance_dim_id
, database_item_function = stu_rec.database_item_function
, save_run_balance_enabled = stu_rec.save_run_balance_enabled
, start_date_code = stu_rec.start_date_code
where balance_dimension_id = stu_rec.c_surrogate_key;
insert into pay_balance_dimensions
(dimension_name
,balance_dimension_id
,route_id
,legislation_code
,legislation_subgroup
,database_item_suffix
,dimension_type
,description
,feed_checking_code
,feed_checking_type
,payments_flag
,expiry_checking_code
,expiry_checking_level
,dimension_level
,period_type
,asg_action_balance_dim_id
,database_item_function
,save_run_balance_enabled
,start_date_code
)
values
(stu_rec.c_true_key
,stu_rec.c_surrogate_key
,stu_rec.route_id
,stu_rec.c_leg_code
,stu_rec.c_leg_sgrp
,stu_rec.database_item_suffix
,stu_rec.dimension_type
,stu_rec.description
,stu_rec.feed_checking_code
,stu_rec.feed_checking_type
,stu_rec.payments_flag
,stu_rec.expiry_checking_code
,stu_rec.expiry_checking_level
,stu_rec.dimension_level
,stu_rec.period_type
,stu_rec.asg_action_balance_dim_id
,stu_rec.database_item_function
,stu_rec.save_run_balance_enabled
,stu_rec.start_date_code);
IF p_phase = 1 THEN update_uid; END IF;
l_null_return varchar2(1); -- used for select null stmts
CURSOR stu -- selects all rows from startup entity
IS
select BALANCE_DIMENSION_ID
, ROUTE_ID
, ROUTE_TYPE
, PRIORITY
, RUN_DIMENSION_ID
, BALANCE_TYPE_COLUMN
, DECODE_REQUIRED
, rowid
from hr_s_dimension_routes;
stu_rec stu%ROWTYPE; -- Record for the above select
delete from hr_s_dimension_routes
where rowid = stu_rec.rowid;
insert_hr_stu_exceptions('pay_dimension_routes'
, stu_rec.BALANCE_DIMENSION_ID
, exception_type
, to_char(stu_rec.ROUTE_ID));
select distinct null
into l_null_return
from hr_s_balance_dimensions
where balance_dimension_id = stu_rec.balance_dimension_id;
select distinct null
into l_null_return
from pay_balance_dimensions
where balance_dimension_id = stu_rec.balance_dimension_id;
select distinct null
into l_null_return
from hr_s_routes
where route_id = stu_rec.route_id;
select distinct null
into l_null_return
from ff_routes
where route_id = stu_rec.route_id;
select route_id, run_dimension_id
into l_route_id, l_run_dimension_id
from pay_dimension_routes
where BALANCE_DIMENSION_ID=stu_rec.BALANCE_DIMENSION_ID
and PRIORITY = stu_rec.PRIORITY;
delete ff_compiled_info_f fci
where fci.formula_id in (
select fdi.formula_id
from ff_fdi_usages_f fdi
where FDI.usage = 'D'
and exists (select null from
ff_database_items dbi
where fdi.item_name = dbi.user_name
and exists (select null from
ff_user_entities ent
where ent.user_entity_id = dbi.user_entity_id
and ent.creator_type in ('B', 'RB')
and ent.route_id = stu_rec.route_id)));
delete ff_fdi_usages_f fdi
where FDI.usage = 'D'
and exists (select null from
ff_database_items dbi
where fdi.item_name = dbi.user_name
and exists (select null from
ff_user_entities ent
where ent.user_entity_id = dbi.user_entity_id
and ent.creator_type in ('B', 'RB')
and ent.route_id = stu_rec.route_id));
delete from ff_user_entities ue
where creator_type in ('B', 'RB')
and route_id = stu_rec.ROUTE_ID;
delete pay_dimension_routes
where BALANCE_DIMENSION_ID=stu_rec.BALANCE_DIMENSION_ID
and PRIORITY = stu_rec.PRIORITY;
insert into pay_dimension_routes
(BALANCE_DIMENSION_ID
,ROUTE_ID
,ROUTE_TYPE
,PRIORITY
,RUN_DIMENSION_ID
,BALANCE_TYPE_COLUMN
,DECODE_REQUIRED)
values
(stu_rec.BALANCE_DIMENSION_ID
,stu_rec.ROUTE_ID
,stu_rec.ROUTE_TYPE
,stu_rec.PRIORITY
,stu_rec.RUN_DIMENSION_ID
,stu_rec.BALANCE_TYPE_COLUMN
,stu_rec.DECODE_REQUIRED);
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select org_information_type
, description
, destination
, displayed_org_information_type doit
, legislation_code
, navigation_method
, fnd_application_id
, rowid
from hr_s_org_information_types;
stu_rec stu%ROWTYPE; -- Record for above SELECT
delete from hr_s_org_information_types
where rowid = stu_rec.rowid;
delete from hr_s_org_info_types_by_class
where org_information_type = stu_rec.org_information_type;
select null
into l_null_return
from hr_org_information_types
where org_information_type = stu_rec.org_information_type
and nvl(destination,'X') = nvl(stu_rec.destination,'X')
and nvl(displayed_org_information_type,'X') = nvl(stu_rec.doit,'X')
and nvl(legislation_code,'X') = nvl(stu_rec.legislation_code,'X')
and nvl(navigation_method,'X') = nvl(stu_rec.navigation_method,'X');
select null
into l_null_return
from dual
where not exists
((select ORG_CLASSIFICATION,
ORG_INFORMATION_TYPE,
MANDATORY_FLAG,
ENABLED_FLAG
from hr_s_org_info_types_by_class
where org_information_type = stu_rec.org_information_type
MINUS
select ORG_CLASSIFICATION,
ORG_INFORMATION_TYPE,
MANDATORY_FLAG,
ENABLED_FLAG
from hr_org_info_types_by_class
where org_information_type = stu_rec.org_information_type
)
UNION
(select ORG_CLASSIFICATION,
ORG_INFORMATION_TYPE,
MANDATORY_FLAG,
ENABLED_FLAG
from hr_org_info_types_by_class
where org_information_type = stu_rec.org_information_type
MINUS
select ORG_CLASSIFICATION,
ORG_INFORMATION_TYPE,
MANDATORY_FLAG,
ENABLED_FLAG
from hr_s_org_info_types_by_class
where org_information_type = stu_rec.org_information_type
));
update hr_org_information_types
set destination = stu_rec.destination
, displayed_org_information_type = stu_rec.doit
, legislation_code = stu_rec.legislation_code
, navigation_method = stu_rec.navigation_method
where org_information_type = stu_rec.org_information_type;
insert into hr_org_information_types
(org_information_type
,description
,destination
,displayed_org_information_type
,fnd_application_id
,legislation_code
,navigation_method
)
values
(stu_rec.org_information_type
,stu_rec.description
,stu_rec.destination
,stu_rec.doit
,stu_rec.fnd_application_id
,stu_rec.legislation_code
,stu_rec.navigation_method
);
delete from hr_org_info_types_by_class
where org_information_type = stu_rec.org_information_type;
insert into hr_org_info_types_by_class
(ORG_CLASSIFICATION
,ORG_INFORMATION_TYPE
,MANDATORY_FLAG
,ENABLED_FLAG
)
select org_classification
, org_information_type
, mandatory_flag
, 'Y' -- default to Y at least for now
from hr_s_org_info_types_by_class
where org_information_type = stu_rec.org_information_type;
l_null_return varchar2(1); -- For 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select information_type c_true_key
, active_inactive_flag
, description
, legislation_code c_leg_code
, request_id
, program_application_id
, program_id
, program_update_date
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, rowid
, multiple_occurences_flag
from hr_s_assignment_info_types;
stu_rec stu%ROWTYPE; -- Cursor for earlier select
insert_hr_stu_exceptions('per_assignment_info_types'
, 0
, exception_type
, stu_rec.c_true_key);
delete from hr_s_assignment_info_types
where rowid = stu_rec.rowid;
-- Check if a delivered row is needed and insert into the
-- live tables if it is
BEGIN
-- Perform a check to see if this primary key has been installed
-- with a legislation code that would make it visible at the same time
-- as this row. Ie: if any legislation code is null within the set of
-- returned rows, then the transfer may not go ahead. If no rows are
-- returned then the delivered row is fine.
BEGIN
select distinct null
into l_null_return
from per_assignment_info_types
where information_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);
-- delivery tables, otherwise insert into the live table.
BEGIN
select distinct null
into l_null_return
from per_assignment_info_types
where legislation_code = stu_rec.c_leg_code
and information_type = stu_rec.c_true_key;
insert into per_assignment_info_types
(information_type
,active_inactive_flag
,description
,legislation_code
,request_id
,program_application_id
,program_id
,program_update_date
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,multiple_occurences_flag
)
values
(stu_rec.c_true_key
,stu_rec.active_inactive_flag
,stu_rec.description
,stu_rec.c_leg_code
,stu_rec.request_id
,stu_rec.program_application_id
,stu_rec.program_id
,stu_rec.program_update_date
,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.multiple_occurences_flag
);
l_null_return varchar2(1); -- used for 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select field_name
, legislation_code
, prompt
, validation_name
, validation_type
, target_location
, rule_mode
, rule_type
, PROMPT_MESSAGE
, IN_LINE_MESSAGE
, QUICK_TIP_MESSAGE
, BUBBLE_TIP_MESSAGE
, category
, rowid
from hr_s_legislative_field_info;
stu_rec stu%ROWTYPE; -- Record for the above select
delete from hr_s_legislative_field_info
where rowid = stu_rec.rowid;
-- Check if a delivered row is needed and insert into the
-- live tables if it is.
l_prompt pay_legislative_field_info.prompt%type;
select prompt
, validation_type
, rule_mode
, PROMPT_MESSAGE
, IN_LINE_MESSAGE
, QUICK_TIP_MESSAGE
, BUBBLE_TIP_MESSAGE
, category
, target_location
, rowid
into l_prompt
, l_val_type
, l_rule_mode
, l_PROMPT_MESSAGE
, l_IN_LINE_MESSAGE
, l_QUICK_TIP_MESSAGE
, l_BUBBLE_TIP_MESSAGE
, l_category
, l_target_location
, l_rowid
from pay_legislative_field_info
where field_name = stu_rec.field_name
and legislation_code = stu_rec.legislation_code
and rule_type = stu_rec.rule_type
and nvl(target_location,'~') = nvl(stu_rec.target_location,'~')
and nvl(validation_name,'~') = nvl(stu_rec.validation_name,'~');
update pay_legislative_field_info
set
prompt = stu_rec.prompt
, validation_name = stu_rec.validation_name
, validation_type = stu_rec.validation_type
, target_location = stu_rec.target_location
, rule_mode = stu_rec.rule_mode
, PROMPT_MESSAGE = stu_rec.PROMPT_MESSAGE
, IN_LINE_MESSAGE = stu_rec.IN_LINE_MESSAGE
, QUICK_TIP_MESSAGE = stu_rec.QUICK_TIP_MESSAGE
, BUBBLE_TIP_MESSAGE = stu_rec.BUBBLE_TIP_MESSAGE
, CATEGORY = stu_rec.CATEGORY
where rowid = l_rowid;
-- Row needs to be inserted
IF phase = 1 THEN return; END IF;
insert into pay_legislative_field_info
(field_name
,legislation_code
,prompt
,validation_name
,validation_type
,target_location
,rule_mode
,rule_type
,PROMPT_MESSAGE
,IN_LINE_MESSAGE
,QUICK_TIP_MESSAGE
,BUBBLE_TIP_MESSAGE
,CATEGORY
)
values
(stu_rec.field_name
,stu_rec.legislation_code
,stu_rec.prompt
,stu_rec.validation_name
,stu_rec.validation_type
,stu_rec.target_location
,stu_rec.rule_mode
,stu_rec.rule_type
,stu_rec.PROMPT_MESSAGE
,stu_rec.IN_LINE_MESSAGE
,stu_rec.QUICK_TIP_MESSAGE
,stu_rec.BUBBLE_TIP_MESSAGE
,stu_rec.CATEGORY
);
l_null_return varchar2(1); -- Used for 'select null' statements
select payment_type_id c_surrogate_key
, territory_code
, currency_code
, category
, payment_type_name c_true_key
, allow_as_default
, description
, pre_validation_required
, procedure_name
, validation_days
, validation_value
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, rowid
from hr_s_payment_types;
stu_rec stu%ROWTYPE; -- Record for above SELECT
-- 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 count(*)
into cnt
from hr_s_payment_types;
select distinct null
into l_null_return
from pay_payment_types a
where exists
(select null
from hr_s_payment_types b
where a.payment_type_id = b.payment_type_id
);
--update all payment_type_id's to remove conflict
update /*+NO_INDEX*/ hr_s_payment_types
set payment_type_id = payment_type_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'PAYMENT_TYPE_ID';
select min(payment_type_id) - (count(*) *3)
, max(payment_type_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_payment_types;
select max(payment_type_id)
into v_max_live
from pay_payment_types;
select pay_payment_types_s.nextval
into v_sequence_number
from dual;
insert_hr_stu_exceptions ('pay_payment_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 payment_type_id
into l_new_surrogate_key
from pay_payment_types
where payment_type_name = stu_rec.c_true_key
and ((territory_code is NULL and stu_rec.territory_code is NULL)
or stu_rec.territory_code= territory_code);
select pay_payment_types_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_payment_types
set payment_type_id = l_new_surrogate_key
where payment_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 = 'PAYMENT_TYPE_ID';
END update_uid;
delete from hr_s_payment_types
where rowid = stu_rec.rowid;
-- Check if a delivered row is needed and insert into the
-- live tables if it is.
--
-- #310520. Change to update the row if it already exists. This
-- differs from the previous functionality, which only ever did
-- inserts, and wouldn't handle updates.
--
BEGIN
IF p_phase = 1 THEN
return;
update pay_payment_types
set payment_type_id = stu_rec.c_surrogate_key
, currency_code = stu_rec.currency_code
, category = stu_rec.category
, allow_as_default = stu_rec.allow_as_default
, description = stu_rec.description
, pre_validation_required = stu_rec.pre_validation_required
, procedure_name = stu_rec.procedure_name
, validation_days = stu_rec.validation_days
, validation_value = stu_rec.validation_value
, 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 payment_type_name = stu_rec.c_true_key
and ((territory_code is NULL and stu_rec.territory_code is NULL)
or stu_rec.territory_code= territory_code);
insert into pay_payment_types
(payment_type_id
,territory_code
,currency_code
,category
,payment_type_name
,allow_as_default
,description
,pre_validation_required
,procedure_name
,validation_days
,validation_value
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(stu_rec.c_surrogate_key
,stu_rec.territory_code
,stu_rec.currency_code
,stu_rec.category
,stu_rec.c_true_key
,stu_rec.allow_as_default
,stu_rec.description
,stu_rec.pre_validation_required
,stu_rec.procedure_name
,stu_rec.validation_days
,stu_rec.validation_value
,stu_rec.last_update_date
,stu_rec.last_updated_by
,stu_rec.last_update_login
,stu_rec.created_by
,stu_rec.creation_date
);
IF p_phase = 1 THEN update_uid; END IF;
select r1.effective_start_date
, r1.user_row_id c_surrogate_key
, r1.row_low_range_or_name c_true_key
, r1.row_high_range
, r1.legislation_code
, r1.legislation_subgroup
, r1.user_table_id
from hr_s_user_rows_f r1
where not exists(
select null
from hr_s_user_rows_f r2
where r2.user_row_id = r1.user_row_id
and r2.effective_start_date < r1.effective_start_date);
-- 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_user_rows_f
where user_row_id = pc_user_row_id;
-- select all child user column instances for the current user row
select *
from hr_s_user_column_instances_f
where user_row_id = p_user_row_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 count(*)
into cnt
from hr_s_user_rows_f;
select distinct null
into l_null_return
from pay_user_rows_f a
where exists
(select null
from hr_s_user_rows_f b
where a.user_row_id = b.user_row_id
);
--update all user_row_id's to remove conflict
update /*+NO_INDEX*/ hr_s_user_rows_f
set user_row_id = user_row_id - 50000000;
update /*+NO_INDEX*/ hr_s_user_column_instances_f
set user_row_id = user_row_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'USER_ROW_ID';
select min(user_row_id) - (count(*) *3)
, max(user_row_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_user_rows_f;
select max(user_row_id)
into v_max_live
from pay_user_rows_f;
select pay_user_rows_s.nextval
into v_sequence_number
from dual;
insert_hr_stu_exceptions('pay_user_rows_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_user_rows_f
where user_row_id = v_id;
delete from hr_s_user_column_instances_f
where user_row_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 user_row_id
into l_new_user_row_id
from pay_user_rows_f
where user_table_id = r_distinct.user_table_id
and row_low_range_or_name = r_distinct.c_true_key
and nvl(row_high_range, 'NULL') =
nvl(r_distinct.row_high_range, 'NULL')
and effective_start_date = r_distinct.effective_start_date
and business_Group_id is null
and nvl(legislation_code, 'x') = nvl(r_distinct.legislation_code, 'x');
select pay_user_rows_s.nextval
into l_new_user_row_id
from dual;
update hr_s_user_rows_f
set user_row_id = l_new_user_row_id
where user_row_id = r_distinct.c_surrogate_key;
update hr_s_application_ownerships
set key_value = to_char(l_new_user_row_id)
where key_value = to_char(r_distinct.c_surrogate_key)
and key_name = 'USER_ROW_ID';
update hr_s_user_column_instances_f
set user_row_id = l_new_user_row_id
where user_row_id = r_distinct.c_surrogate_key;
END update_uid;
-- every foreign key. The first select from the delivery tables.
-- If a row is found 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
-- Check first parent does not exist in the delivery tables
select null
into l_null_return
from hr_s_user_tables
where user_table_id = r_each_row.user_table_id;
select null
into l_null_return
from pay_user_tables
where user_table_id = r_each_row.user_table_id;
-- Add the user table id and row high range to the select criteria.
--
-- Further fix necessary to cater for the possibility that the
-- high range may be null. Must put NVL on both sides, otherwise
-- matching rows with null row_high_ranges are not detected.
--
select distinct null
into l_null_return
from pay_user_rows_f a
where a.user_table_id = r_distinct.user_table_id
and a.effective_start_date = r_distinct.effective_start_date
and a.row_low_range_or_name = r_distinct.c_true_key
and nvl(row_high_range, 'NULL') =
nvl(r_distinct.row_high_range, 'NULL')
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));
-- Add the user table id and row high range to the select criteria.
--
--
-- Further fix necessary to cater for the possibility that the
-- high range may be null. Must put NVL on both sides, otherwise
-- matching rows with null row_high_ranges are not detected.
--
select distinct null
into l_null_return
from pay_user_rows_f
where row_low_range_or_name = r_distinct.c_true_key
and effective_start_date = r_distinct.effective_start_date
and nvl(row_high_range, 'NULL') =
nvl(r_distinct.row_high_range, 'NULL')
and user_table_id = r_distinct.user_table_id
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 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.
-- 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; END IF;
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 = 'USER_ROW_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 = 'USER_ROW_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'
);
-- Get new surrogate id and update child references
update_uid;
delete from pay_user_column_instances_f
where user_row_id = r_distinct.c_surrogate_key
and business_group_id is null;
delete from pay_user_rows_f
where user_row_id = r_distinct.c_surrogate_key;
insert into pay_user_rows_f
(user_row_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,user_table_id
,row_low_range_or_name
,display_sequence
,legislation_subgroup
,row_high_range
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(each_row.user_row_id
,each_row.effective_start_date
,each_row.effective_end_date
,each_row.business_group_id
,each_row.legislation_code
,each_row.user_table_id
,each_row.row_low_range_or_name
,each_row.display_sequence
,each_row.legislation_subgroup
,each_row.row_high_range
,each_row.last_update_date
,each_row.last_updated_by
,each_row.last_update_login
,each_row.created_by
,each_row.creation_date
);
select distinct null
into l_null_return
from pay_user_columns
where user_column_id = each_child.user_column_id;
insert into pay_user_column_instances_f
(user_column_instance_id
,effective_start_date
,effective_end_date
,user_row_id
,user_column_id
,business_group_id
,legislation_code
,legislation_subgroup
,value
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
select pay_user_column_instances_s.nextval
,each_child.effective_start_date
,each_child.effective_end_date
,each_child.user_row_id
,each_child.user_column_id
,each_child.business_group_id
,each_child.legislation_code
,each_child.legislation_subgroup
,each_child.value
,each_child.last_update_date
,each_child.last_updated_by
,each_child.last_update_login
,each_child.created_by
,each_child.creation_date
from dual;
-- is within the correct range, rows will be inserted/updated to the
-- history table. If the routine has been called in pahse 2, a row must
-- exist in the history table.
cursor c_legs is
select package_name
from hr_s_history;
-- First insert a row into the hr_stu_history table
FOR r_legs in c_legs loop
v_package_name := r_legs.package_name;
delete from hr_stu_exceptions;
update hr_stu_history
set status = 'Phase '||p_phase
where package_name = v_package_name;
insert into hr_stu_history
(package_name
,date_of_export
,date_of_import
,status
,legislation_code
)
select distinct package_name
, date_of_export
, sysdate
, 'Phase 1'
, legislation_code
from hr_s_history
where package_name = v_package_name;
l_null_return varchar2(1); -- Used for 'select null' statements
select monetary_unit_id c_surrogate_key
, currency_code
, business_group_id
, legislation_code c_leg_code
, monetary_unit_name c_true_key
, relative_value
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, rowid
from hr_s_monetary_units;
stu_rec stu%ROWTYPE; -- Record definition for above SELECT
insert_hr_stu_exceptions('PAY_MONETARY_UNITS'
, 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 count(*)
into cnt
from hr_s_monetary_units;
select distinct null
into l_null_return
from pay_monetary_units a
where exists
(select null
from hr_s_monetary_units b
where a.monetary_unit_id = b.monetary_unit_id
);
--update all monetary_unit_id's to remove conflict
update hr_s_monetary_units
set monetary_unit_id = monetary_unit_id - 50000000;
update hr_s_application_ownerships
set key_value = key_value - 50000000
where key_name = 'MONETARY_UNIT_ID';
select min(monetary_unit_id) - (count(*) *3)
, max(monetary_unit_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_monetary_units;
select max(monetary_unit_id)
into v_max_live
from pay_monetary_units;
select pay_monetary_units_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 monetary_unit_id
into l_new_surrogate_key
from pay_monetary_units
where currency_code = stu_rec.currency_code
and monetary_unit_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_monetary_units_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_monetary_units
set monetary_unit_id = l_new_surrogate_key
where monetary_unit_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 = 'MONETARY_UNIT_ID';
END update_uid;
delete from hr_s_monetary_units
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.
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 = 'MONETARY_UNIT_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.
-- The procedure checks to see if the same monetary unit has been
-- installed in a contentious business group or legislation.
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 pay_monetary_units a
where a.monetary_unit_name = stu_rec.c_true_key
and a.currency_code = stu_rec.currency_code
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_monetary_units
where monetary_unit_name = stu_rec.c_true_key
and currency_code = stu_rec.currency_code
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 p_phase = 1 THEN return; END IF; --only insert on phase 2
-- 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.
update pay_monetary_units
set currency_code = stu_rec.currency_code
, business_group_id = null
, legislation_code = stu_rec.c_leg_code
, relative_value = stu_rec.relative_value
, 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 monetary_unit_id = stu_rec.c_surrogate_key;
insert into pay_monetary_units
(monetary_unit_id
,currency_code
,business_group_id
,legislation_code
,monetary_unit_name
,relative_value
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
values
(stu_rec.c_surrogate_key
,stu_rec.currency_code
,stu_rec.business_group_id
,stu_rec.c_leg_code
,stu_rec.c_true_key
,stu_rec.relative_value
,stu_rec.last_update_date
,stu_rec.last_updated_by
,stu_rec.last_update_login
,stu_rec.created_by
,stu_rec.creation_date
);
-- if the row is required then the surrogate id is updated and the
-- main transfer logic is called.
IF p_phase = 1 THEN check_next_sequence; END IF;
IF p_phase = 1 THEN update_uid; END IF;
l_null_return varchar2(1); -- used for 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select report_type ,
report_qualifier ,
report_format ,
effective_start_date ,
effective_end_date ,
range_code ,
assignment_action_code ,
initialization_code ,
archive_code ,
magnetic_code ,
report_category ,
report_name ,
sort_code ,
updatable_flag ,
deinitialization_code ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date ,
temporary_action_flag ,
rowid
from hr_s_report_format_mappings_f;
delete from hr_s_report_format_mappings_f
where rowid = stu_rec.rowid;
-- this updates uses only report_type,qualifier,category as its primary key
-- it may be that effective start and end dates will need to be added
-- but as of know we can see no need for this
update pay_report_format_mappings_f
set effective_start_date=stu_rec.effective_start_date
, effective_end_date=stu_rec.effective_end_date
, range_code=stu_rec.range_code
, assignment_action_code=stu_rec.assignment_action_code
, initialization_code=stu_rec.initialization_code
, archive_code=stu_rec.archive_code
, magnetic_code=stu_rec.magnetic_code
, report_name=stu_rec.report_name
, sort_code=stu_rec.sort_code
, updatable_flag=stu_rec.updatable_flag
, report_format=stu_rec.report_format
, deinitialization_code=stu_rec.deinitialization_code
, 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
, temporary_action_flag = stu_rec.temporary_action_flag
where report_type= stu_rec.report_type
and report_qualifier=stu_rec.report_qualifier
and report_category=stu_rec.report_category
and effective_start_date = stu_rec.effective_start_date
and effective_end_date = stu_rec.effective_end_date;
insert into pay_report_format_mappings_f
( report_type ,
report_qualifier ,
report_format ,
effective_start_date ,
effective_end_date ,
range_code ,
assignment_action_code ,
initialization_code ,
archive_code ,
magnetic_code ,
report_category ,
report_name ,
sort_code ,
updatable_flag ,
deinitialization_code ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date ,
temporary_action_flag
)
values
( stu_rec.report_type ,
stu_rec.report_qualifier ,
stu_rec.report_format ,
stu_rec.effective_start_date ,
stu_rec.effective_end_date ,
stu_rec.range_code ,
stu_rec.assignment_action_code ,
stu_rec.initialization_code ,
stu_rec.archive_code ,
stu_rec.magnetic_code ,
stu_rec.report_category ,
stu_rec.report_name ,
stu_rec.sort_code ,
stu_rec.updatable_flag ,
stu_rec.deinitialization_code ,
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.temporary_action_flag
);
l_null_return varchar2(1); -- used for 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select distinct magnetic_block_id c_surrogate_key,
block_name c_true_key,
main_block_flag,
report_format,
cursor_name,
no_column_returned
from hr_s_magnetic_blocks;
select count(*)
into cnt
from hr_s_magnetic_blocks;
select distinct null
into l_null_return
from pay_magnetic_blocks a
where exists
(select null
from hr_s_magnetic_blocks b
where a.magnetic_block_id = b.magnetic_block_id
);
update hr_s_magnetic_blocks
set magnetic_block_id=magnetic_block_id -50000000;
update hr_s_magnetic_records
set magnetic_block_id=magnetic_block_id -50000000;
update hr_s_magnetic_records
set next_block_id=next_block_id -50000000;
select min(magnetic_block_id) - (count(*) *3)
, max(magnetic_block_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_magnetic_blocks;
select max(magnetic_block_id)
into v_max_live
from pay_magnetic_blocks;
select pay_magnetic_blocks_s.nextval
into v_sequence_number
from dual;
insert_hr_stu_exceptions('pay_magnetic_blocks'
, stu_rec.c_surrogate_key
, exception_type
, stu_rec.c_true_key);
PROCEDURE update_uid
--------------------
IS
BEGIN
BEGIN
select distinct magnetic_block_id
into l_new_surrogate_key
from pay_magnetic_blocks
where replace(ltrim(rtrim(upper(block_name))), ' ', '_') =
replace(ltrim(rtrim(upper(stu_rec.c_true_key))), ' ', '_')
and replace(ltrim(rtrim(upper(report_format))), ' ', '_') =
replace(ltrim(rtrim(upper(stu_rec.report_format))), ' ', '_')
and replace(ltrim(rtrim(upper(nvl(cursor_name,'X')))), ' ', '_') =
replace(ltrim(rtrim(upper(nvl(stu_rec.cursor_name,'X')))), ' ', '_');
select pay_magnetic_blocks_s.nextval
into l_new_surrogate_key
from dual;
-- Update all child entities
update hr_s_magnetic_blocks
set magnetic_block_id = l_new_surrogate_key
where magnetic_block_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 = 'MAGNETIC_BLOCK_ID';
update hr_s_magnetic_records
set magnetic_block_id = l_new_surrogate_key
where magnetic_block_id = stu_rec.c_surrogate_key;
update hr_s_magnetic_records
set next_block_id= l_new_surrogate_key
where next_block_id=stu_rec.c_surrogate_key;
END update_uid;
delete from hr_s_magnetic_blocks
where magnetic_block_id = stu_rec.c_surrogate_key;
-- if a magnetic_block has changed then the row will be updated,
-- if it is new , it gets inserted.
IS
BEGIN
BEGIN
select distinct null
into l_null_return
from pay_magnetic_blocks
where magnetic_block_id =l_new_surrogate_key
and block_name = stu_rec.c_true_key
and main_block_flag =stu_rec.main_block_flag
and report_format =stu_rec.report_format
and nvl(cursor_name,'')=nvl(stu_rec.cursor_name,'')
and nvl(no_column_returned,0)=nvl(stu_rec.no_column_returned,0);
update pay_magnetic_blocks
set block_name=stu_rec.c_true_key
, main_block_flag=stu_rec.main_block_flag
, report_format=stu_rec.report_format
, cursor_name=stu_rec.cursor_name
, no_column_returned=stu_rec.no_column_returned
where magnetic_block_id =stu_rec.c_surrogate_key;
-- No row there to update, must insert
BEGIN
insert into pay_magnetic_blocks
( magnetic_block_id ,
block_name ,
main_block_flag ,
report_format ,
cursor_name ,
no_column_returned)
values
( stu_rec.c_surrogate_key ,
stu_rec.c_true_key ,
stu_rec.main_block_flag ,
stu_rec.report_format ,
stu_rec.cursor_name ,
stu_rec.no_column_returned);
IF p_phase = 1 THEN update_uid; END IF;
l_null_return varchar2(1); -- used for 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select distinct
formula_id ,
magnetic_block_id ,
next_block_id ,
overflow_mode ,
sequence ,
frequency ,
last_run_executed_mode
from hr_s_magnetic_records;
delete from hr_s_magnetic_records
where magnetic_block_id = stu_rec.magnetic_block_id
and sequence = stu_rec.sequence;
select distinct null
into l_null_return
from pay_magnetic_records
where formula_id=stu_rec.formula_id
and magnetic_block_id=stu_rec.magnetic_block_id
and next_block_id=stu_rec.next_block_id
and overflow_mode=stu_rec.overflow_mode
and sequence=stu_rec.sequence
and frequency=stu_rec.frequency
and last_run_executed_mode=stu_rec.last_run_executed_mode;
update pay_magnetic_records
set formula_id=stu_rec.formula_id
, next_block_id=stu_rec.next_block_id
, overflow_mode=stu_rec.overflow_mode
, frequency=stu_rec.frequency
, last_run_executed_mode=stu_rec.last_run_executed_mode
where magnetic_block_id=stu_rec.magnetic_block_id
and sequence=stu_rec.sequence;
-- No row there to update, must insert
BEGIN
insert into pay_magnetic_records
( formula_id ,
magnetic_block_id ,
next_block_id ,
overflow_mode ,
sequence ,
frequency ,
last_run_executed_mode)
values
( stu_rec.formula_id ,
stu_rec.magnetic_block_id ,
stu_rec.next_block_id ,
stu_rec.overflow_mode ,
stu_rec.sequence ,
stu_rec.frequency ,
stu_rec.last_run_executed_mode);
l_null_return varchar2(1); -- used for 'select null' statements
CURSOR stu -- Selects all rows from startup entity
IS
select report_type ,
report_qualifier ,
report_category ,
user_entity_id ,
effective_start_date ,
effective_end_date ,
archive_type ,
updatable_flag ,
display_sequence ,
report_format_item_id c_surrogate_key,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date ,
rowid
from hr_s_report_format_items_f;
select count(*)
into cnt
from hr_s_report_format_items_f;
select distinct null
into l_null_return
from pay_report_format_items_f a
where exists
(select null
from hr_s_report_format_items_f b
where a.report_format_item_id = b.report_format_item_id
);
update hr_s_report_format_items_f
set report_format_item_id=report_format_item_id -50000000;
select min(report_format_item_id) - (count(*) *3)
, max(report_format_item_id) + (count(*) *3)
into v_min_delivered
, v_max_delivered
from hr_s_report_format_items_f;
select max(report_format_item_id)
into v_max_live
from pay_report_format_items_f;
select pay_report_format_items_s.nextval
into v_sequence_number
from dual;
PROCEDURE update_uid
--------------------
IS
BEGIN
BEGIN
select distinct report_format_item_id
into l_new_surrogate_key
from pay_report_format_items_f
where report_type = stu_rec.report_type
and report_qualifier = stu_rec.report_qualifier
and report_category = stu_rec.report_category
and user_entity_id = stu_rec.user_entity_id;
select pay_report_format_items_s.nextval
into l_new_surrogate_key
from dual;
select pay_report_format_items_s.nextval
into l_new_surrogate_key
from dual;
update hr_s_report_format_items_f
set report_format_item_id = l_new_surrogate_key
where report_type = stu_rec.report_type
and report_qualifier = stu_rec.report_qualifier
and report_category = stu_rec.report_category
and user_entity_id = stu_rec.user_entity_id;
END update_uid;
delete from hr_s_report_format_items_f
where rowid = stu_rec.rowid;
update pay_report_format_items_f
set effective_start_date=stu_rec.effective_start_date
, effective_end_date=stu_rec.effective_end_date
, archive_type=stu_rec.archive_type
, updatable_flag=stu_rec.updatable_flag
, display_sequence=stu_rec.display_sequence
, report_format_item_id = stu_rec.c_surrogate_key
, 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 report_type= stu_rec.report_type
and report_qualifier=stu_rec.report_qualifier
and report_category=stu_rec.report_category
and user_entity_id=stu_rec.user_entity_id
and effective_start_date = stu_rec.effective_start_date
and effective_end_date = stu_rec.effective_end_date ;
insert into pay_report_format_items_f
( report_type ,
report_qualifier ,
report_category ,
user_entity_id ,
effective_start_date ,
effective_end_date ,
archive_type ,
updatable_flag ,
display_sequence ,
report_format_item_id ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date
)
values
( stu_rec.report_type ,
stu_rec.report_qualifier ,
stu_rec.report_category ,
stu_rec.user_entity_id ,
stu_rec.effective_start_date ,
stu_rec.effective_end_date ,
stu_rec.archive_type ,
stu_rec.updatable_flag ,
stu_rec.display_sequence ,
stu_rec.c_surrogate_key ,
stu_rec.last_update_date ,
stu_rec.last_updated_by ,
stu_rec.last_update_login ,
stu_rec.created_by ,
stu_rec.creation_date
);
IF p_phase = 1 THEN update_uid; END IF;
select count(*)
into v_exception_counter
from hr_stu_exceptions;
update hr_stu_history
set status = 'Phase '||p_phase||' has exceptions raised'
where package_name in
(select package_name
from hr_s_history);
update hr_stu_history a
set a.status = 'Complete'
where exists
(select null
from hr_s_history b
where b.package_name = a.package_name
and b.date_of_export = a.date_of_export
);
select
BT.BALANCE_TYPE_ID,
L.LANGUAGE_CODE TRANS_LANG,
B.LANGUAGE_CODE BASE_LANG,
BT.BALANCE_NAME,
BT.REPORTING_NAME,
BT.LAST_UPDATE_DATE,
BT.LAST_UPDATED_BY,
BT.LAST_UPDATE_LOGIN,
BT.CREATED_BY,
BT.CREATION_DATE
from PAY_BALANCE_TYPES BT,
FND_LANGUAGES L,
FND_LANGUAGES B
where L.INSTALLED_FLAG in ('I', 'B')
and B.INSTALLED_FLAG = 'B'
and NVL(TO_CHAR(BT.BUSINESS_GROUP_ID),'Null Value')='Null Value'
and (
not exists (
select '1'
from pay_balance_types_tl btt
where btt.balance_type_id = bt.balance_type_id
and btt.language = l.language_code)
or exists (select '1' from pay_balance_types_tl btt2
where btt2.balance_type_id = bt.balance_type_id
and btt2.language = b.language_code
and nvl(btt2.reporting_name,'XXX') <>
nvl(bt.reporting_name,'XXX'))
);
delete PAY_BALANCE_TYPES_TL
where rowid in (select pbttl.rowid
from pay_balance_types pbt,
pay_balance_types_tl pbttl
where pbt.balance_type_id = pbttl.balance_type_id
and nvl(pbt.reporting_name, 'XXX') <>
nvl(pbttl.reporting_name, 'XXX')
and NVL(TO_CHAR(PBT.BUSINESS_GROUP_ID),'Null Value')='Null Value'
and pbttl.BALANCE_TYPE_ID = l_rec.BALANCE_TYPE_ID
and pbttl.language = l_rec.trans_lang);
insert into PAY_BALANCE_TYPES_TL
(
BALANCE_TYPE_ID,
LANGUAGE,
SOURCE_LANG,
BALANCE_NAME,
REPORTING_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
)
select
l_rec.BALANCE_TYPE_ID,
l_rec.TRANS_LANG,
l_rec.BASE_LANG,
l_rec.BALANCE_NAME,
l_rec.REPORTING_NAME,
l_rec.LAST_UPDATE_DATE,
l_rec.LAST_UPDATED_BY,
l_rec.LAST_UPDATE_LOGIN,
l_rec.CREATED_BY,
l_rec.CREATION_DATE
from dual
where not exists (
select '1'
from pay_balance_types_tl btt
where btt.balance_type_id = l_rec.balance_type_id
and btt.language = l_rec.trans_lang);
select
EC.CLASSIFICATION_ID,
EC.CLASSIFICATION_NAME,
EC.DESCRIPTION,
L.LANGUAGE_CODE TRANS_LANG,
B.LANGUAGE_CODE BASE_LANG,
EC.LAST_UPDATE_DATE,
EC.LAST_UPDATED_BY,
EC.LAST_UPDATE_LOGIN,
EC.CREATED_BY,
EC.CREATION_DATE
from PAY_ELEMENT_CLASSIFICATIONS EC,
FND_LANGUAGES L,
FND_LANGUAGES B
where L.INSTALLED_FLAG in ('I', 'B')
and B.INSTALLED_FLAG = 'B'
and NVL(TO_CHAR(EC.BUSINESS_GROUP_ID),'Null Value')='Null Value'
and
( not exists (
select '1'
from PAY_ELEMENT_CLASSIFICATIONS_TL ECT
where ECT.CLASSIFICATION_ID = EC.CLASSIFICATION_ID
and ECT.language = l.language_code)
or exists (select '1' from PAY_ELEMENT_CLASSIFICATIONS_TL ect2
where ect2.CLASSIFICATION_ID = ec.CLASSIFICATION_ID
and ect2.language = b.language_code
and nvl( ect2.description,'ec.description' ||'1') <> nvl(ec.description,ect2.description || '-1'))
);
delete PAY_ELEMENT_CLASSIFICATIONS_TL
where rowid in (select ectl.rowid
from PAY_ELEMENT_CLASSIFICATIONS ec,
PAY_ELEMENT_CLASSIFICATIONS_TL ectl
where ec.CLASSIFICATION_ID = ectl.CLASSIFICATION_ID
and nvl(ec.DESCRIPTION, 'XXX') <>
nvl(ectl.DESCRIPTION, 'XXX')
and NVL(TO_CHAR(ec.BUSINESS_GROUP_ID),'Null Value')='Null Value'
and ectl.CLASSIFICATION_ID = l_rec.CLASSIFICATION_ID
and ectl.language = l_rec.trans_lang);
insert into PAY_ELEMENT_CLASSIFICATIONS_TL
(
CLASSIFICATION_ID,
CLASSIFICATION_NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
)
select
l_rec.CLASSIFICATION_ID,
l_rec.CLASSIFICATION_NAME,
l_rec.DESCRIPTION,
l_rec.TRANS_LANG,
l_rec.base_lang,
l_rec.LAST_UPDATE_DATE,
l_rec.LAST_UPDATED_BY,
l_rec.LAST_UPDATE_LOGIN,
l_rec.CREATED_BY,
l_rec.CREATION_DATE
from dual
where not exists (
select '1'
from PAY_ELEMENT_CLASSIFICATIONS_TL ECT
where ECT.CLASSIFICATION_ID = l_rec.CLASSIFICATION_ID
and ECT.language = l_rec.trans_lang);
select
ET.ELEMENT_TYPE_ID,
ET.ELEMENT_NAME,
ET.REPORTING_NAME,
ET.DESCRIPTION,
L.LANGUAGE_CODE TRANS_LANG,
B.LANGUAGE_CODE BASE_LANG,
ET.LAST_UPDATE_DATE,
ET.LAST_UPDATED_BY,
ET.LAST_UPDATE_LOGIN,
ET.CREATED_BY,
ET.CREATION_DATE
from PAY_ELEMENT_TYPES_F ET,
FND_LANGUAGES L,
FND_LANGUAGES B
where L.INSTALLED_FLAG in ('I', 'B')
and B.INSTALLED_FLAG = 'B'
and NVL(TO_CHAR(ET.BUSINESS_GROUP_ID),'Null Value')='Null Value'
and
( not exists (
select '1'
from PAY_ELEMENT_TYPES_F_TL ETT
where ETT.ELEMENT_TYPE_ID = ET.ELEMENT_TYPE_ID
and ETT.language = l.language_code)
or exists (select '1' from PAY_ELEMENT_TYPES_F_TL ett2
where ett2.ELEMENT_TYPE_ID = et.ELEMENT_TYPE_ID
and ett2.language = b.language_code
and (nvl(ett2.reporting_name, 'XXX') <> nvl(et.reporting_name, 'XXX')
or
nvl(ett2.description, 'XXX') <> nvl(et.description, 'XXX')))
);
delete PAY_ELEMENT_TYPES_F_TL
where rowid in (select pettl.rowid
from pay_element_types_f pet,
pay_element_types_f_tl pettl
where pet.element_type_id = pettl.element_type_id
and (
(nvl(pet.reporting_name, 'XXX') <>
nvl(pettl.reporting_name, 'XXX'))
or
(nvl(pet.description, 'XXX') <>
nvl(pettl.description, 'XXX'))
)
and NVL(TO_CHAR(PET.BUSINESS_GROUP_ID),'Null Value')='Null Value'
and pettl.ELEMENT_TYPE_ID = l_rec.ELEMENT_TYPE_ID
and pettl.language = l_rec.trans_lang);
insert into PAY_ELEMENT_TYPES_F_TL
(
ELEMENT_TYPE_ID,
ELEMENT_NAME,
REPORTING_NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
)
select
l_rec.ELEMENT_TYPE_ID,
l_rec.ELEMENT_NAME,
l_rec.REPORTING_NAME,
l_rec.DESCRIPTION,
l_rec.TRANS_LANG,
l_rec.base_lang,
l_rec.LAST_UPDATE_DATE,
l_rec.LAST_UPDATED_BY,
l_rec.LAST_UPDATE_LOGIN,
l_rec.CREATED_BY,
l_rec.CREATION_DATE
from dual
where not exists (
select '1'
from PAY_ELEMENT_TYPES_F_TL ETT
where ETT.ELEMENT_TYPE_ID = l_rec.ELEMENT_TYPE_ID
and ETT.language = l_rec.trans_lang);
select
IV.INPUT_VALUE_ID,
IV.NAME,
L.LANGUAGE_CODE TRANS_LANG,
B.LANGUAGE_CODE BASE_LANG,
IV.LAST_UPDATE_DATE,
IV.LAST_UPDATED_BY,
IV.LAST_UPDATE_LOGIN,
IV.CREATED_BY,
IV.CREATION_DATE
from PAY_INPUT_VALUES_F IV,
FND_LANGUAGES L,
FND_LANGUAGES B
where L.INSTALLED_FLAG in ('I', 'B')
and B.INSTALLED_FLAG = 'B'
and NVL(TO_CHAR(IV.BUSINESS_GROUP_ID),'Null Value')='Null Value'
and not exists (
select '1'
from PAY_INPUT_VALUES_F_TL IVT
where IVT.INPUT_VALUE_ID = IV.INPUT_VALUE_ID
and IVT.language = l.language_code);
select flv.meaning
into l_translated_value
from fnd_lookup_values flv
where flv.lookup_type = 'NAME_TRANSLATIONS'
and flv.lookup_code = 'PAY VALUE'
and flv.view_application_id = 3
and flv.security_group_id = 0
and flv.language = l_rec.trans_lang;
insert into PAY_INPUT_VALUES_F_TL
(
INPUT_VALUE_ID,
NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
)
select
l_rec.INPUT_VALUE_ID,
l_translated_value,
l_rec.TRANS_LANG,
l_rec.base_lang,
l_rec.LAST_UPDATE_DATE,
l_rec.LAST_UPDATED_BY,
l_rec.LAST_UPDATE_LOGIN,
l_rec.CREATED_BY,
l_rec.CREATION_DATE
from dual
where not exists (
select '1'
from PAY_INPUT_VALUES_F_TL IVT
where IVT.INPUT_VALUE_ID = l_rec.INPUT_VALUE_ID
and IVT.language = l_rec.trans_lang);
select
PT.PAYMENT_TYPE_ID,
PT.PAYMENT_TYPE_NAME,
PT.DESCRIPTION,
L.LANGUAGE_CODE TRANS_LANG,
B.LANGUAGE_CODE BASE_LANG,
PT.LAST_UPDATE_DATE,
PT.LAST_UPDATED_BY,
PT.LAST_UPDATE_LOGIN,
PT.CREATED_BY,
PT.CREATION_DATE
from PAY_PAYMENT_TYPES PT,
FND_LANGUAGES L,
FND_LANGUAGES B
where L.INSTALLED_FLAG in ('I', 'B')
and B.INSTALLED_FLAG = 'B'
and not exists (
select '1'
from PAY_PAYMENT_TYPES_TL PTT
where PTT.PAYMENT_TYPE_ID = PT.PAYMENT_TYPE_ID
and PTT.language = l.language_code);
insert into PAY_PAYMENT_TYPES_TL
(
PAYMENT_TYPE_ID,
PAYMENT_TYPE_NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
)
select
l_rec.PAYMENT_TYPE_ID,
l_rec.PAYMENT_TYPE_NAME,
l_rec.DESCRIPTION,
l_rec.TRANS_LANG,
l_rec.base_lang,
l_rec.LAST_UPDATE_DATE,
l_rec.LAST_UPDATED_BY,
l_rec.LAST_UPDATE_LOGIN,
l_rec.CREATED_BY,
l_rec.CREATION_DATE
from dual
where not exists (
select '1'
from PAY_PAYMENT_TYPES_TL PTT
where PTT.PAYMENT_TYPE_ID = l_rec.PAYMENT_TYPE_ID
and PTT.language = l_rec.trans_lang);
select
M.INFORMATION_TYPE,
L.LANGUAGE_CODE TRANS_LANG,
B.LANGUAGE_CODE BASE_LANG,
M.DESCRIPTION,
M.LAST_UPDATE_DATE,
M.LAST_UPDATED_BY,
M.LAST_UPDATE_LOGIN,
M.CREATED_BY,
M.CREATION_DATE
from PER_ASSIGNMENT_INFO_TYPES M,
FND_LANGUAGES L,
FND_LANGUAGES B
where L.INSTALLED_FLAG in ('I', 'B')
and B.INSTALLED_FLAG = 'B'
and not exists ( select '1'
from per_assignment_info_types_tl pait
where pait.information_type = m.information_type
and pait.language = l.language_code);
insert into PER_ASSIGNMENT_INFO_TYPES_TL
(
INFORMATION_TYPE,
LANGUAGE,
SOURCE_LANG,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
)
select
l_rec.INFORMATION_TYPE,
l_rec.TRANS_LANG,
l_rec.base_lang,
l_rec.DESCRIPTION,
l_rec.LAST_UPDATE_DATE,
l_rec.LAST_UPDATED_BY,
l_rec.LAST_UPDATE_LOGIN,
l_rec.CREATED_BY,
l_rec.CREATION_DATE
from dual
where not exists ( select '1'
from per_assignment_info_types_tl pait
where pait.information_type =
l_rec.information_type
and pait.language = l_rec.trans_lang);
select
M.ASSIGNMENT_STATUS_TYPE_ID,
L.LANGUAGE_CODE TRANS_LANG,
B.LANGUAGE_CODE BASE_LANG,
M.USER_STATUS,
M.LAST_UPDATE_DATE,
M.LAST_UPDATED_BY,
M.LAST_UPDATE_LOGIN,
M.CREATED_BY,
M.CREATION_DATE
from PER_ASSIGNMENT_STATUS_TYPES M,
FND_LANGUAGES L,
FND_LANGUAGES B
where L.INSTALLED_FLAG in ('I', 'B')
and B.INSTALLED_FLAG = 'B'
and NVL(TO_CHAR(M.BUSINESS_GROUP_ID),'Null Value')='Null Value'
and not exists (
select '1'
from per_assignment_status_types_tl past
where past.assignment_status_type_id =
m.assignment_status_type_id
and past.language = l.language_code);
insert into PER_ASSIGNMENT_STATUS_TYPES_TL
(
ASSIGNMENT_STATUS_TYPE_ID,
LANGUAGE,
SOURCE_LANG,
USER_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
)
select
l_rec.ASSIGNMENT_STATUS_TYPE_ID,
l_rec.TRANS_LANG,
l_rec.base_lang,
l_rec.USER_STATUS,
l_rec.LAST_UPDATE_DATE,
l_rec.LAST_UPDATED_BY,
l_rec.LAST_UPDATE_LOGIN,
l_rec.CREATED_BY,
l_rec.CREATION_DATE
from dual
where not exists (
select '1'
from per_assignment_status_types_tl past
where past.assignment_status_type_id =
l_rec.assignment_status_type_id
and past.language = l_rec.trans_lang);
select
M.MONETARY_UNIT_ID,
L.LANGUAGE_CODE TRANS_LANG,
B.LANGUAGE_CODE BASE_LANG,
M.MONETARY_UNIT_NAME,
M.LAST_UPDATE_DATE,
M.LAST_UPDATED_BY,
M.LAST_UPDATE_LOGIN,
M.CREATED_BY,
M.CREATION_DATE
from PAY_MONETARY_UNITS M,
FND_LANGUAGES L,
FND_LANGUAGES B
where L.INSTALLED_FLAG in ('I', 'B')
and B.INSTALLED_FLAG = 'B'
and NVL(TO_CHAR(M.BUSINESS_GROUP_ID),'Null Value')='Null Value'
and not exists (
select '1'
from PAY_MONETARY_UNITS_TL pmut
where pmut.monetary_unit_id =
m.monetary_unit_id
and pmut.language = l.language_code);
insert into PAY_MONETARY_UNITS_TL
(
MONETARY_UNIT_ID,
LANGUAGE,
SOURCE_LANG,
MONETARY_UNIT_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
)
select
l_rec.MONETARY_UNIT_ID,
l_rec.TRANS_LANG,
l_rec.base_lang,
l_rec.MONETARY_UNIT_NAME,
l_rec.LAST_UPDATE_DATE,
l_rec.LAST_UPDATED_BY,
l_rec.LAST_UPDATE_LOGIN,
l_rec.CREATED_BY,
l_rec.CREATION_DATE
from dual
where not exists (
select '1'
from PAY_MONETARY_UNITS_TL pmut
where pmut.monetary_unit_id =
l_rec.monetary_unit_id
and pmut.language = l_rec.trans_lang);
select
bc.BALANCE_CATEGORY_ID,
L.LANGUAGE_CODE TRANS_LANG,
B.LANGUAGE_CODE BASE_LANG,
bc.CATEGORY_NAME,
bc.LAST_UPDATE_DATE,
bc.LAST_UPDATED_BY,
bc.LAST_UPDATE_LOGIN,
bc.CREATED_BY,
bc.CREATION_DATE
from PAY_BALANCE_CATEGORIES_F bc,
FND_LANGUAGES L,
FND_LANGUAGES B
where L.INSTALLED_FLAG in ('I', 'B')
and B.INSTALLED_FLAG = 'B'
and not exists (
select '1'
from PAY_BALANCE_CATEGORIES_F_TL bct
where bct.BALANCE_CATEGORY_ID = bc.BALANCE_CATEGORY_ID
and bct.language = l.language_code);
insert into PAY_BALANCE_CATEGORIES_F_TL
(
BALANCE_CATEGORY_ID,
LANGUAGE,
SOURCE_LANG,
USER_CATEGORY_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
)
select
l_rec.BALANCE_CATEGORY_ID,
l_rec.TRANS_LANG,
l_rec.base_lang,
l_rec.CATEGORY_NAME,
l_rec.LAST_UPDATE_DATE,
l_rec.LAST_UPDATED_BY,
l_rec.LAST_UPDATE_LOGIN,
l_rec.CREATED_BY,
l_rec.CREATION_DATE
from dual
where not exists (
select '1'
from PAY_BALANCE_CATEGORIES_F_TL bct
where bct.BALANCE_CATEGORY_ID = l_rec.BALANCE_CATEGORY_ID
and bct.language = l_rec.trans_lang);
select distinct legislation_code
from hr_s_history;
select count(*)
into g_debug_cnt
from pay_patch_status
where patch_name = 'HRGLOBAL_DEBUG';
select count(*)
into v_exception_counter
from hr_stu_exceptions;
select count(*)
into v_exception_counter
from hr_stu_exceptions;
select distinct legislation_code
into l_leg_code
from hr_s_history
where legislation_code = 'ZZ';