[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_table_data (P_BUSINESS_GROUP_ID_ITD IN number,
P_LEGISLATION_CODE_ITD IN varchar2,
P_APPLICATION_ID_ITD IN number,
P_RANGE_OR_MATCH_ITD IN varchar2,
P_USER_KEY_UNITS_ITD IN varchar2,
P_USER_TABLE_NAME_ITD IN varchar2,
P_USER_ROW_TITLE_ITD IN varchar2)
IS
v_table_rowid varchar2(100);
hr_utility.trace ('PROCEDURE insert_data entered.');
PAY_USER_TABLES_PKG.INSERT_ROW
(P_ROWID => v_table_rowid,
P_USER_TABLE_ID => v_user_table_id,
P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID_ITD,
P_LEGISLATION_CODE => P_LEGISLATION_CODE_ITD,
P_LEGISLATION_SUBGROUP => NULL,
P_RANGE_OR_MATCH => P_RANGE_OR_MATCH_ITD,
P_USER_KEY_UNITS => P_USER_KEY_UNITS_ITD,
P_USER_TABLE_NAME => P_USER_TABLE_NAME_ITD,
P_USER_ROW_TITLE => P_USER_ROW_TITLE_ITD);
hr_utility.trace ('PROCEDURE insert_data exiting.');
END insert_table_data;
SELECT user_table_name
FROM pay_user_tables
WHERE business_group_id = P_BUSINESS_GROUP_ID_CT
AND user_table_name = P_USER_TABLE_NAME_CT;
SELECT legislation_code
INTO v_legislation_code
FROM per_business_groups
WHERE business_group_id = P_BUSINESS_GROUP_ID_CT;
hr_utility.trace ('inserting data into '||P_USER_TABLE_NAME_CT||' from create_table.');
insert_table_data (P_BUSINESS_GROUP_ID_ITD => P_BUSINESS_GROUP_ID_CT,
P_LEGISLATION_CODE_ITD => v_legislation_code,
P_APPLICATION_ID_ITD => P_APPLICATION_ID_CT,
P_RANGE_OR_MATCH_ITD => P_RANGE_OR_MATCH_CT,
P_USER_KEY_UNITS_ITD => P_USER_KEY_UNITS_CT,
P_USER_TABLE_NAME_ITD => P_USER_TABLE_NAME_CT,
P_USER_ROW_TITLE_ITD => P_USER_ROW_TITLE_CT);
SELECT user_column_id
FROM pay_user_columns
WHERE user_table_id =
(SELECT user_table_id
FROM pay_user_tables
WHERE business_group_id = P_BUSINESS_GROUP_ID_CC
AND user_table_name = P_USER_TABLE_NAME_CC)
AND user_column_name = P_USER_COLUMN_NAME_CC;
SELECT user_table_id
INTO v_user_table_id
FROM pay_user_tables
WHERE user_table_name = P_USER_TABLE_NAME_CC
AND business_group_id = P_BUSINESS_GROUP_ID_CC;
SELECT legislation_code
INTO v_legislation_code
FROM per_business_groups
WHERE business_group_id = P_BUSINESS_GROUP_ID_CC;
PAY_USER_COLUMNS_PKG.INSERT_ROW
(P_ROWID => v_column_row_id,
P_USER_COLUMN_ID => v_user_column_id,
P_USER_TABLE_ID => v_user_table_id,
P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID_CC,
P_LEGISLATION_CODE => v_legislation_code,
P_LEGISLATION_SUBGROUP => NULL,
P_USER_COLUMN_NAME => P_USER_COLUMN_NAME_CC,
P_FORMULA_ID => NULL);
SELECT user_row_id
FROM pay_user_rows_f
WHERE user_table_id = (SELECT user_table_id
FROM pay_user_tables
WHERE user_table_name = P_USER_TABLE_NAME_CR
AND business_group_id = P_BUSINESS_GROUP_ID_CR)
AND P_ROW_LOW_RANGE_OR_NAME_CR = row_low_range_or_name;
SELECT user_column_instance_id
FROM pay_user_column_instances_f
WHERE user_row_id = p_user_row_id
AND user_column_id = p_user_column_id;
SELECT user_table_id, legislation_code
INTO v_user_table_id, v_legislation_code
FROM pay_user_tables
WHERE user_table_name = P_USER_TABLE_NAME_CR
AND business_group_id = P_BUSINESS_GROUP_ID_CR;
SELECT pay_user_rows_s.nextval
INTO v_user_row_id
FROM dual;
-- this insertion creates the row
hr_utility.trace ('Inserting row '||P_ROW_LOW_RANGE_OR_NAME_CR);
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)
VALUES (v_user_row_id,
v_start_date,
v_end_date,
P_BUSINESS_GROUP_ID_CR,
v_legislation_code,
v_user_table_id,
P_ROW_LOW_RANGE_OR_NAME_CR,
P_DISPLAY_SEQUENCE_CR,
NULL,
NULL);
SELECT user_column_id
INTO v_user_column_id
FROM pay_user_columns_v
WHERE user_column_name = P_USER_COLUMN_NAME_CR
AND user_table_id = v_user_table_id;
hr_utility.set_location ('inserting instance', 10);
PAY_USER_COLUMN_INSTANCES_PKG.INSERT_ROW
(P_ROWID => v_column_instance_rowid
,P_USER_COLUMN_INSTANCE_ID => v_user_column_instance_id
,P_EFFECTIVE_START_DATE => v_start_date
,P_EFFECTIVE_END_DATE => v_end_date
,P_USER_ROW_ID => v_user_row_id
,P_USER_COLUMN_ID => v_user_column_id
,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID_CR
,P_LEGISLATION_CODE => v_legislation_code
,P_LEGISLATION_SUBGROUP => NULL
,P_VALUE => P_VALUE_CR);
SELECT lookup_code, meaning
FROM fnd_common_lookups
WHERE lookup_type = P_LOOKUP_TYPE;
SELECT user_person_type
FROM per_person_types_v
WHERE business_group_id = P_BUSINESS_GROUP_ID;
(select count(*)
from
(select p2.person_id,sum(bal2.run_amount) run_amount
from
per_person_types_tl pt2
, per_all_people_f p2
, per_bf_balances_v bal2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where p2.business_group_id = P_BUSINESS_GROUP_ID
and userenv(''lang'')=pt2.language
and bal2.assignment_id = a2.assignment_id
and bal2.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal2.run_amount is not null
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.establishment_id in P_ESTABLISHMENT_LIST
RESTRICTION.PRIMARY_FLAG[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and bal2.processing_date between
a2.effective_start_date and a2.effective_end_date
and p2.person_id = a2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and bal2.processing_date between
p2.effective_start_date and p2.effective_end_date
and bal2.business_group_id = P_BUSINESS_GROUP_ID
RESTRICTION.BALANCE_NAME[bal2]
group by p2.person_id) high
where high.run_amount
>=
sum(bal.run_amount)
and high.person_id <>
p.person_id
)
');
, (select count(distinct p4.person_id) population
from per_person_types_tl pt4
, per_all_people_f p4
, per_assignment_status_types ast4
, per_all_assignments_f a4
where a4.assignment_status_type_id = ast4.assignment_status_type_id
and userenv(''lang'')=pt4.language
and a4.assignment_type = ''E''
and a4.establishment_id in P_ESTABLISHMENT_LIST
RESTRICTION.PRIMARY_FLAG[a4]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast4]
and P_START_OF_YEAR <= a4.effective_end_date
and P_END_OF_YEAR >= a4.effective_start_date
and p4.person_id = a4.person_id
and p4.person_type_id = pt4.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p4*/]
and a4.effective_end_date >= p4.effective_start_date
and a4.effective_start_date <= p4.effective_end_date) pop
where bal.assignment_id =
a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and a.assignment_status_type_id = per_ast.assignment_status_type_id
and a.assignment_type = ''E''
and a.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[per_ast]
and bal.processing_date between
a.effective_start_date and a.effective_end_date
and p.person_id = a.person_id
and p.person_type_id = pt.person_type_id
and userenv(''lang'')=pt.language
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p*/]
and bal.processing_date between
p.effective_start_date and p.effective_end_date
and bal.business_group_id = P_BUSINESS_GROUP_ID
RESTRICTION.BALANCE_NAME[bal]
group by p.person_id
, decode(pop.population,0,0,greatest(round(pop.population*0.1),1))
having decode(pop.population,0,0,greatest(round(pop.population*0.1),1)) >
(select count(*)
from
(select p2.person_id,sum(bal2.run_amount) run_amount
from per_bf_balances_v bal2
, per_person_types_tl pt2
,
per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where p2.business_group_id = P_BUSINESS_GROUP_ID
and userenv(''lang'')=pt2.language
and bal2.assignment_id = a2.assignment_id
and bal2.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal2.run_amount is not null
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.establishment_id in P_ESTABLISHMENT_LIST
RESTRICTION.PRIMARY_FLAG[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and bal2.processing_date between
a2.effective_start_date and a2.effective_end_date
and p2.person_id = a2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and bal2.processing_date between
p2.effective_start_date and p2.effective_end_date
and bal2.business_group_id = P_BUSINESS_GROUP_ID
RESTRICTION.BALANCE_NAME[bal2]
group by p2.person_id) high
where high.run_amount >=
sum(bal.run_amount)
and high.person_id <>
p.person_id
)
');
, (select count(distinct p4.person_id) population
from per_person_types_tl pt4
, per_all_people_f p4
, per_assignment_status_types ast4
, per_all_assignments_f a4
where a4.assignment_status_type_id = ast4.assignment_status_type_id
and userenv(''lang'')=pt4.language
and a4.assignment_type = ''E''
and a4.establishment_id in P_ESTABLISHMENT_LIST
RESTRICTION.PRIMARY_FLAG[a4]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast4]
and P_START_OF_YEAR <= a4.effective_end_date
and P_END_OF_YEAR >= a4.effective_start_date
and p4.person_id = a4.person_id
and p4.person_type_id = pt4.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p4*/]
and a4.effective_end_date >= p4.effective_start_date
and a4.effective_start_date <= p4.effective_end_date) pop
where bal.assignment_id =
a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and a.assignment_status_type_id = per_ast.assignment_status_type_id
and a.assignment_type = ''E''
and a.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[per_ast]
and bal.processing_date between
a.effective_start_date and a.effective_end_date
and p.person_id = a.person_id
and p.person_type_id = pt.person_type_id
and userenv(''lang'')=pt.language
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p*/]
and bal.processing_date between
p.effective_start_date and p.effective_end_date
and bal.business_group_id = P_BUSINESS_GROUP_ID
RESTRICTION.BALANCE_NAME[bal]
group by p.person_id
, decode(pop.population,0,0,greatest(round(pop.population*0.1),1))
having decode(pop.population,0,0,greatest(round(pop.population*0.1),1)) >
(select count(*)
from
(select p2.person_id,sum(bal2.run_amount) run_amount
from per_bf_balances_v bal2
, per_person_types_tl pt2
,
per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where p2.business_group_id = P_BUSINESS_GROUP_ID
and userenv(''lang'')=pt2.language
and bal2.assignment_id = a2.assignment_id
and bal2.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal2.run_amount is not null
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.establishment_id in P_ESTABLISHMENT_LIST
RESTRICTION.PRIMARY_FLAG[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and bal2.processing_date between
a2.effective_start_date and a2.effective_end_date
and p2.person_id = a2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and bal2.processing_date between
p2.effective_start_date and p2.effective_end_date
and bal2.business_group_id = P_BUSINESS_GROUP_ID
RESTRICTION.BALANCE_NAME[bal2]
group by p2.person_id) high
where high.run_amount <=
sum(bal.run_amount)
and high.person_id <>
p.person_id
)
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and
a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and bal.balance_name = abt.name
RESTRICTION.ABSENCE_CATEGORY[abt]
and abt.business_group_id = P_BUSINESS_GROUP_ID
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and a3.assignment_status_type_id=ast3.assignment_status_type_id
and userenv(''lang'')=pt3.language
and a3.assignment_type = ''E''
and a3.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id =
v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id =
v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and userenv(''lang'')=pt3.language
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and userenv(''lang'')=pt2.language
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and
a3.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and
a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and
a3.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where p.person_id = p2.person_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= p2.effective_start_date
and p2.effective_end_date >= P_START_OF_YEAR
and a2.person_id = p2.person_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
RESTRICTION.PRIMARY_FLAG[a2]
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and p.person_id = pma.person_id
and pma.consultation_date between
P_START_OF_YEAR and P_END_OF_YEAR
RESTRICTION.MEDICAL_EXAMINATION_TYPE[pma]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and to_date(P_YEAR||''1231'',''YYYYMMDD'')>= a2.effective_start_date
and to_date(P_YEAR||''1201'',''YYYYMMDD'') <= a2.effective_end_date
and to_date(P_YEAR||''1231'',''YYYYMMDD'')>= p2.effective_start_date
and to_date(P_YEAR||''1201'',''YYYYMMDD'') <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
and bal.assignment_id = a.assignment_id
and bal.processing_date
between to_date(P_YEAR||''1201'',''YYYYMMDD'') and
to_date(P_YEAR||''1231'',''YYYYMMDD'')
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where
a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where p.person_id = p2.person_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.person_id = p2.person_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
RESTRICTION.PRIMARY_FLAG[a2]
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and p.person_id = pma.person_id
and pma.consultation_date between
P_START_OF_YEAR and P_END_OF_YEAR
RESTRICTION.MEDICAL_EXAMINATION_TYPE[pma]
RESTRICTION.MEDICAL_EXAMINATION_RESULT[pma]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and
a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and a.period_of_service_id = per_pos.period_of_service_id
and ( (a.effective_end_date = per_pos.actual_termination_date
RESTRICTION.TERM_LEAVE_REASON[per_pos]) or (a.effective_end_date <>
per_pos.actual_termination_date RESTRICTION.ASG_LEAVE_REASON[scl]) )
and a.contract_id = c.contract_id(+)
and least(a.effective_end_date,P_END_OF_YEAR)
between nvl(c.effective_start_date,least(a.effective_end_date,P_END_OF_YEAR))
and nvl(c.effective_end_date,least(a.effective_end_date,P_END_OF_YEAR))
RESTRICTION.CONTRACT_TYPE[c]
RESTRICTION.CONTRACT_CATEGORY[c]
and P_END_OF_YEAR >= (select max(a3.effective_end_date)
from per_assignment_status_types ast3
, per_all_assignments_f a3
where a3.person_id = a.person_id
and P_END_OF_YEAR >= a3.effective_start_date
and P_START_OF_YEAR <= a3.effective_end_date
and a3.establishment_id = a.establishment_id
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.primary_flag = ''Y''
--RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and ast3.per_system_status = ''ACTIVE_ASSIGN''
and not exists
(select null
from per_assignment_status_types ast4
, per_all_assignments_f a4
where a4.person_id = a.person_id
and a4.effective_start_date - 1 = a3.effective_end_date
and a4.primary_flag = ''Y''
and a4.assignment_status_type_id = ast4.assignment_status_type_id
and ast4.per_system_status <> ''TERM_ASSIGN''
and exists
(select null
from hr_organization_information oi1
, hr_organization_information oi2
where oi1.organization_id = a4.establishment_id
and oi1.org_information_context = ''FR_ESTAB_INFO''
and oi2.organization_id = a3.establishment_id
and oi2.org_information_context = ''FR_ESTAB_INFO''
and oi1.org_information1
=
oi2.org_information1)))
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and to_date(P_YEAR||''0331'',''YYYYMMDD'') between a2.effective_start_date
and a2.effective_end_date
and to_date(P_YEAR||''0331'',''YYYYMMDD'') between p2.effective_start_date
and p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and exists
(select null
from per_disabilities_f pdf
where p.person_id = pdf.person_id
and to_date(P_YEAR||''0331'',''YYYYMMDD'') between pdf.effective_start_date
and pdf.effective_end_date
RESTRICTION.COTOREP_CLASS[pdf]
RESTRICTION.DISABILITY_RATE[pdf])
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id =
v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_bf_balances_v bal
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and userenv(''lang'')=pt3.language
and a3.assignment_type = ''E''
and a3.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.assignment_id = a3.assignment_id
and bal.run_amount is not null
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and p3.effective_end_date
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
RESTRICTION.BALANCE_NAME[bal]
)
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a2.assignment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and userenv(''lang'')=pt2.language
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.primary_flag = ''Y''
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
and P_END_OF_YEAR >= a2.effective_start_date
and P_END_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_END_OF_YEAR <=
p2.effective_end_date
and p2.person_id = p.person_id
and p2.effective_start_date <= a2.effective_start_date
and p2.effective_end_date >= a2.effective_start_date
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and p.person_id = pwi.person_id
and pwi.incident_date between
P_START_OF_YEAR and
P_END_OF_YEAR
RESTRICTION.WORK_ACCIDENT_TYPE[pwi]
RESTRICTION.WORK_ACCIDENT_CODE[pwi]
RESTRICTION.WORK_ACCIDENT_RESULT[pwi]
');
(select null
from hr_soft_coding_keyflex scl2
, per_contracts_f c2
, per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.person_id = p.person_id
and userenv(''lang'')=pt2.language
and a2.establishment_id in P_ESTABLISHMENT_LIST
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
RESTRICTION.PRIMARY_FLAG[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and a2.contract_id = c2.contract_id(+)
RESTRICTION.CONTRACT_CATEGORY[c2]
RESTRICTION.CONTRACT_TYPE[c2]
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
and a2.soft_coding_keyflex_id = scl2.soft_coding_keyflex_id(+)
and P_END_OF_YEAR+1 <= a2.effective_end_date
and P_END_OF_YEAR+1 >= a2.effective_start_date
and P_END_OF_YEAR+1 <=
p2.effective_end_date
and P_END_OF_YEAR+1 >= p2.effective_start_date
and P_END_OF_YEAR+1 <= nvl(c2.effective_end_date,P_END_OF_YEAR+1)
and P_END_OF_YEAR+1 >=
nvl(c2.effective_start_date,P_END_OF_YEAR+1)))
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a2.assignment_id
and userenv(''lang'')=pt2.language
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.primary_flag = ''Y''
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
and P_END_OF_YEAR >= a2.effective_start_date
and P_END_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_END_OF_YEAR <= p2.effective_end_date
and p2.person_id =
p.person_id
and p2.effective_start_date <= a2.effective_start_date
and p2.effective_end_date >= a2.effective_start_date
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and exists
(select null
from per_work_incidents pwi
where p.person_id = pwi.person_id
and pwi.incident_date between
P_START_OF_YEAR and P_END_OF_YEAR
RESTRICTION.WORK_ACCIDENT_TYPE[pwi]
RESTRICTION.WORK_ACCIDENT_RESULT[pwi])
');
OR exists (select null
from hr_soft_coding_keyflex scl2
, per_person_types_tl pt2
,
per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.person_id = p.person_id
and userenv(''lang'')=pt2.language
and a2.soft_coding_keyflex_id = scl2.soft_coding_keyflex_id(+)
and a2.establishment_id in P_ESTABLISHMENT_LIST
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
RESTRICTION.WORK_PATTERN[scl2]
and a2.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and P_END_OF_YEAR+1 >= a2.effective_start_date
and P_END_OF_YEAR+1 <= a2.effective_end_date
and P_END_OF_YEAR+1 >= p2.effective_start_date
and P_END_OF_YEAR+1 <=
p2.effective_end_date))
');
, (select ''01'' month from sys.dual
union select ''02'' month from sys.dual
union select ''03'' month from sys.dual
union select ''04'' month from sys.dual
union select ''05'' month from sys.dual
union select ''06'' month from sys.dual
union select ''07'' month from sys.dual
union select ''08'' month from sys.dual
union select ''09'' month from sys.dual
union select ''10'' month from sys.dual
union select ''11'' month from sys.dual
union select ''12'' month from sys.dual) m
where a.establishment_id = v.establishment_id
and a.assignment_status_type_id = per_ast.assignment_status_type_id
and a.assignment_type = ''E''
and userenv(''lang'')=pt.language
RESTRICTION.PRIMARY_FLAG[a]
RESTRICTION.EMPLOYMENT_CATEGORY[a]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[per_ast]
and a.contract_id = c.contract_id (+)
RESTRICTION.CONTRACT_TYPE[c]
RESTRICTION.CONTRACT_CATEGORY[c]
and a.person_id =
p.person_id
and p.person_type_id = pt.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p*/]
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) <= a.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >= a.effective_start_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) <=
nvl(c.effective_end_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1))
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >=
nvl(c.effective_end_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1))
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) <= p.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >= p.effective_start_date
and (least(a.effective_end_date,p.effective_end_date,
nvl(c.effective_end_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')+1,1)))
> add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)
OR exists
(select
null
from per_contracts_f c2
, per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.person_id = p.person_id
and userenv(''lang'')=pt2.language
and a2.establishment_id in P_ESTABLISHMENT_LIST
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
RESTRICTION.PRIMARY_FLAG[a2]
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.contract_id = c2.contract_id (+)
RESTRICTION.CONTRACT_TYPE[c2]
RESTRICTION.CONTRACT_CATEGORY[c2]
and a2.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) <= a2.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) >= a2.effective_start_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) <=
nvl(c2.effective_end_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1))
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) >=
nvl(c2.effective_end_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1))
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) <= p2.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) >=
p2.effective_start_date))
');
, (select ''01'' month from sys.dual
union select ''02'' month from sys.dual
union select ''03'' month from sys.dual
union select ''04'' month from sys.dual
union select ''05'' month from sys.dual
union select ''06'' month from sys.dual
union select ''07'' month from sys.dual
union select ''08'' month from sys.dual
union select ''09'' month from sys.dual
union select ''10'' month from sys.dual
union select ''11'' month from sys.dual
union select ''12'' month from sys.dual) m
where a.establishment_id = v.establishment_id
and userenv(''lang'')=pt.language
and a.assignment_status_type_id = per_ast.assignment_status_type_id
and a.assignment_type = ''E''
RESTRICTION.PRIMARY_FLAG[a]
RESTRICTION.EMPLOYMENT_CATEGORY[a]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[per_ast]
and p.person_type_id = pt.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p*/]
and
a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
and a.contract_id = c.contract_id (+)
RESTRICTION.CONTRACT_TYPE[c]
RESTRICTION.CONTRACT_CATEGORY[c]
and a.person_id = p.person_id
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) <= a.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >= a.effective_start_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) <= p.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >= p.effective_start_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) <=
nvl(c.effective_end_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1))
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >=
nvl(c.effective_start_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1))
and (least(a.effective_end_date,p.effective_end_date,
nvl(c.effective_end_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1)))
>
add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)
OR exists
(select null
from hr_soft_coding_keyflex scl2
, per_contracts_f c2
, per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.person_id = p.person_id
and userenv(''lang'')=pt2.language
and a2.establishment_id in P_ESTABLISHMENT_LIST
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
RESTRICTION.PRIMARY_FLAG[a2]
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and a2.soft_coding_keyflex_id = scl2.soft_coding_keyflex_id (+)
and a2.contract_id = c2.contract_id (+)
RESTRICTION.CONTRACT_TYPE[c2]
RESTRICTION.CONTRACT_CATEGORY[c2]
and a2.person_id = p2.person_id
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) <=
a2.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) >= a2.effective_start_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) <= p2.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) >= p2.effective_start_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) <=
nvl(c2.effective_end_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1))
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) >=
nvl(c2.effective_start_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1))))
');
, (select ''01'' month from sys.dual
union select ''02'' month from sys.dual
union select ''03'' month from sys.dual
union select ''04'' month from sys.dual
union select ''05'' month from sys.dual
union select ''06'' month from sys.dual
union select ''07'' month from sys.dual
union select ''08'' month from sys.dual
union select ''09'' month from sys.dual
union select ''10'' month from sys.dual
union select ''11'' month from sys.dual
union select ''12'' month from sys.dual) m
where (p.effective_start_date,a.effective_start_date) =
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >= a2.effective_start_date
and to_date(P_YEAR||m.month||''01'',''YYYYMMDD'') <= a2.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >= p2.effective_start_date
and to_date(P_YEAR||m.month||''01'',''YYYYMMDD'') <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and userenv(''lang'')=pt.language
and p.person_type_id = pt.person_type_id
and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
and bal.assignment_id = a.assignment_id
and bal.processing_date
between to_date(P_YEAR||m.month||''01'',''YYYYMMDD'') and
add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and
p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
, (select ''01'' month from sys.dual
union select ''02'' month from sys.dual
union select ''03'' month from sys.dual
union select ''04'' month from sys.dual
union select ''05'' month from sys.dual
union select ''06'' month from sys.dual
union select ''07'' month from sys.dual
union select ''08'' month from sys.dual
union select ''09'' month from sys.dual
union select ''10'' month from sys.dual
union select ''11'' month from sys.dual
union select ''12'' month from sys.dual) m
where a.establishment_id = v.establishment_id
and userenv(''lang'')=pt.language
and a.assignment_status_type_id = per_ast.assignment_status_type_id
and a.assignment_type = ''E''
RESTRICTION.PRIMARY_FLAG[a]
RESTRICTION.EMPLOYMENT_CATEGORY[a]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[per_ast]
and a.contract_id = c.contract_id (+)
RESTRICTION.CONTRACT_TYPE[c]
RESTRICTION.CONTRACT_CATEGORY[c]
and a.person_id =
p.person_id
and p.person_type_id = pt.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p*/]
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) <= a.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >= a.effective_start_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) <= p.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >= p.effective_start_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) <= nvl(c.effective_end_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1))
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >= nvl(c.effective_start_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1))
and (least(a.effective_end_date,p.effective_end_date,nvl(c.effective_end_date,to_date(''47121231'',''YYYYMMDD''))) >
add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)
OR exists
(select null
from per_contracts_f c2
, per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.person_id = p.person_id
and a2.establishment_id in P_ESTABLISHMENT_LIST
and userenv(''lang'')=pt2.language
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
RESTRICTION.PRIMARY_FLAG[a2]
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.contract_id = c2.contract_id (+)
RESTRICTION.CONTRACT_TYPE[c2]
RESTRICTION.CONTRACT_CATEGORY[c2]
and a2.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)+1 <= a2.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)+1 >= a2.effective_start_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)+1 <= p2.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)+1 >= p2.effective_start_date
and
add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)+1 <= nvl(c2.effective_end_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1))
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)+1 >=
nvl(c2.effective_start_date,add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)))
)
');
, (select ''01'' month from sys.dual
union select ''02'' month from sys.dual
union select ''03'' month from sys.dual
union select ''04'' month from sys.dual
union select ''05'' month from sys.dual
union select ''06'' month from sys.dual
union select ''07'' month from sys.dual
union select ''08'' month from sys.dual
union select ''09'' month from sys.dual
union select ''10'' month from sys.dual
union select ''11'' month from sys.dual
union select ''12'' month from sys.dual) m
where a.establishment_id = v.establishment_id
and a.assignment_status_type_id = per_ast.assignment_status_type_id
and a.assignment_type = ''E''
RESTRICTION.PRIMARY_FLAG[a]
RESTRICTION.EMPLOYMENT_CATEGORY[a]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[per_ast]
and a.person_id = p.person_id
and p.person_type_id = pt.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p*/]
and
add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) <= a.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >= a.effective_start_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) <= p.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1) >= p.effective_start_date
and (least(a.effective_end_date,p.effective_end_date)
> add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)
OR exists
(select null
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.person_id = p.person_id
and userenv(''lang'')=pt2.language
and a2.establishment_id in P_ESTABLISHMENT_LIST
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
RESTRICTION.PRIMARY_FLAG[a2]
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and
p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) <= a2.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) >= a2.effective_start_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) <= p2.effective_end_date
and add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD''),1) >=
p2.effective_start_date))
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and
a2.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and a.contract_id = c.contract_id(+)
and least(a.effective_end_date,P_END_OF_YEAR)
between nvl(c.effective_start_date,least(a.effective_end_date,P_END_OF_YEAR))
and nvl(c.effective_end_date,least(a.effective_end_date,P_END_OF_YEAR))
RESTRICTION.CONTRACT_TYPE[c]
RESTRICTION.CONTRACT_CATEGORY[c]
and exists
(select null
from hr_soft_coding_keyflex scl2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a.person_id = a2.person_id
and a2.assignment_type = ''E''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
and
a2.primary_flag = ''Y''
and a2.soft_coding_keyflex_id = scl2.soft_coding_keyflex_id(+)
RESTRICTION.STARTING_REASON[scl2]
and a2.effective_start_date = a.effective_end_date + 1
and P_END_OF_YEAR >= a2.effective_start_date
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.establishment_id <> a.establishment_id
and exists
(select null
from hr_organization_information oi1
, hr_organization_information oi2
where oi1.organization_id = a.establishment_id
and oi1.org_information_context = ''FR_ESTAB_INFO''
and oi2.organization_id = a2.establishment_id
and oi2.org_information_context = ''FR_ESTAB_INFO''
and oi1.org_information1 =
oi2.org_information1))
');
,(select person_id
,effective_start_date
,trunc(months_between(P_END_OF_YEAR,date_of_birth)/12) age
from per_all_people_f) p
, per_all_assignments_f a
where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
and (p.effective_start_date,a.effective_start_date) =
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and
a2.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
RESTRICTION.AGE[p]
and a.contract_id = c.contract_id(+)
and least(a.effective_end_date,P_END_OF_YEAR)
between nvl(c.effective_start_date,least(a.effective_end_date,P_END_OF_YEAR))
and nvl(c.effective_end_date,least(a.effective_end_date,P_END_OF_YEAR))
RESTRICTION.CONTRACT_TYPE[c]
RESTRICTION.CONTRACT_CATEGORY[c]
and P_START_OF_YEAR <= (select min(a3.effective_start_date)
from per_assignment_status_types ast3
, per_all_assignments_f a3
where a3.person_id = a.person_id
and P_END_OF_YEAR >= a3.effective_start_date
and P_START_OF_YEAR <= a3.effective_end_date
and a3.establishment_id = a.establishment_id
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and not exists
(select null
from per_all_assignments_f a4
where a4.person_id = a.person_id
and a4.effective_end_date + 1 = a3.effective_start_date
and a4.primary_flag = ''Y''
and exists
(select null
from hr_organization_information oi1
, hr_organization_information oi2
where oi1.organization_id = a3.establishment_id
and oi1.org_information_context = ''FR_ESTAB_INFO''
and oi2.organization_id = a4.establishment_id
and oi2.org_information_context = ''FR_ESTAB_INFO''
and oi1.org_information1
=
oi2.org_information1)))
');
(select null
from hr_soft_coding_keyflex scl2
, per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.soft_coding_keyflex_id = scl2.soft_coding_keyflex_id(+)
and userenv(''lang'')=pt2.language
and a2.establishment_id in P_ESTABLISHMENT_LIST
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
RESTRICTION.WORK_PATTERN[scl2]
and a2.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and P_END_OF_YEAR+1 <= a2.effective_end_date
and P_END_OF_YEAR+1 >= a2.effective_start_date
and P_END_OF_YEAR+1 <= p2.effective_end_date
and P_END_OF_YEAR+1 >=
p2.effective_start_date))
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')= pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id =
v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and
a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and
bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id =
v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id =
v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id =
v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
OR exists (select null
from hr_soft_coding_keyflex scl2
, per_person_types_tl pt2
,
per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.person_id = p.person_id
and userenv(''lang'')=pt2.language
and a2.soft_coding_keyflex_id = scl2.soft_coding_keyflex_id(+)
and a2.establishment_id in P_ESTABLISHMENT_LIST
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.NORMAL_WORKING_HOURS[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
RESTRICTION.WORK_PATTERN[scl2]
and a2.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and P_END_OF_YEAR+1 >= a2.effective_start_date
and P_END_OF_YEAR+1 <= a2.effective_end_date
and P_END_OF_YEAR+1 >= p2.effective_start_date
and P_END_OF_YEAR+1 <=
p2.effective_end_date))
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a2.assignment_id
and userenv(''lang'')=pt2.language
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.primary_flag = ''Y''
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <=
p2.effective_end_date
and p2.person_id = p.person_id
and p2.effective_start_date <= a2.effective_start_date
and p2.effective_end_date >= a2.effective_start_date
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and p.person_id = pwi.person_id
and pwi.incident_date between
P_START_OF_YEAR and
P_END_OF_YEAR
RESTRICTION.WORK_ACCIDENT_TYPE[pwi]
');
(select count(*) count
from per_contracts_f c5
,
per_all_assignments_f a5
, per_all_people_f p5
where p5.person_id = p.person_id
and p5.person_id = a5.person_id
and a5.assignment_type = ''E''
and a5.primary_flag = ''Y''
and a5.contract_id = c5.contract_id
and a5.effective_start_date <= p5.effective_end_date
and p5.effective_start_date <= a5.effective_end_date
and a5.effective_start_date <= c5.effective_end_date
and c5.effective_start_date <= a5.effective_end_date
and p5.effective_start_date <= c5.effective_end_date
and c5.effective_start_date <= p5.effective_end_date
and least(p5.effective_end_date,a5.effective_end_date,c5.effective_end_date,P_END_OF_YEAR) <=
least(p.effective_end_date,a.effective_end_date,c.effective_end_date,P_END_OF_YEAR)
and least(p5.effective_end_date,a5.effective_end_date,c5.effective_end_date,P_END_OF_YEAR)
>= P_START_OF_YEAR
having (min(greatest(p5.effective_start_date,a5.effective_start_date,c5.effective_start_date,P_START_OF_YEAR))
<= P_START_OF_YEAR)
and count(*) =
(select count(*)
from per_contracts_f
c3
,per_assignment_status_types ast3
,per_all_assignments_f a3
,per_person_types_tl pt3
,per_all_people_f p3
where p3.person_id = p.person_id
and userenv(''lang'')=pt3.language
and a3.person_id = p3.person_id
and a3.effective_start_date <= p3.effective_end_date
and p3.effective_start_date <= a3.effective_end_date
and a3.effective_start_date <= c3.effective_end_date
and c3.effective_start_date <= a3.effective_end_date
and p3.effective_start_date <= c3.effective_end_date
and c3.effective_start_date <= p3.effective_end_date
and least(p3.effective_end_date,a3.effective_end_date,c3.effective_end_date,P_END_OF_YEAR) <=
least(p.effective_end_date,a.effective_end_date,c.effective_end_date,P_END_OF_YEAR)
and least(p3.effective_end_date,a3.effective_end_date,c3.effective_end_date,P_END_OF_YEAR)
>= P_START_OF_YEAR
and a3.establishment_id = v.establishment_id
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and c3.contract_id = a3.contract_id
RESTRICTION.CONTRACT_TYPE[c3]
RESTRICTION.CONTRACT_CATEGORY[c3]
and
a3.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]))
and not exists
(select null
from per_all_assignments_f a4
where a.assignment_id = a4.assignment_id
and a4.primary_flag = ''Y''
and a4.effective_start_date <= P_END_OF_YEAR
and a4.effective_end_date >= P_START_OF_YEAR
and (a4.contract_id is null
or (not exists
(select null
from per_all_assignments_f a6
where a6.person_id = a4.person_id
and a6.primary_flag = ''Y''
and least(a4.effective_end_date+1,P_END_OF_YEAR) between a6.effective_start_date and
a6.effective_end_date
))))
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a2.assignment_id
and userenv(''lang'')=pt2.language
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.primary_flag = ''Y''
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and p2.person_id = p.person_id
and p2.effective_start_date <= a2.effective_start_date
and p2.effective_end_date >= a2.effective_start_date
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and p.person_id = pwi.person_id
and pwi.incident_date between
P_START_OF_YEAR and
P_END_OF_YEAR
RESTRICTION.WORK_ACCIDENT_TYPE[pwi]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= P2.effective_start_date
and P_START_OF_YEAR <= P2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and exists
(select null
from hr_soft_coding_keyflex scl3
, pay_user_column_instances_f i3
, pay_user_rows_f r3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a2.person_id = a3.person_id
and a3.assignment_type = ''E''
and a3.establishment_id = v.establishment_id
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.effective_start_date = a2.effective_end_date + 1
and P_END_OF_YEAR >= a3.effective_start_date
and a3.soft_coding_keyflex_id = scl3.soft_coding_keyflex_id(+)
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
RESTRICTION.PRIMARY_FLAG[a3]
and r3.row_low_range_or_name = scl3.segment2
and r3.user_table_id = t.user_table_id
and r3.business_group_id = P_BUSINESS_GROUP_ID
and a3.effective_start_date between r3.effective_start_date
and r3.effective_end_date
and i3.user_row_id = r3.user_row_id
and i3.user_column_id = c.user_column_id
and a3.effective_start_date between i3.effective_start_date
and i3.effective_end_date
and to_number(i.value) > to_number(i3.value)))
and a.person_id = p.person_id
and t.user_table_name = ''FR_EMPLOYEE_CATEGORY''
and t.business_group_id = P_BUSINESS_GROUP_ID
and c.user_column_name = ''BS_EMP_CAT_HIERARCHY''
and c.user_table_id = t.user_table_id
and c.business_group_id =
P_BUSINESS_GROUP_ID
and r.row_low_range_or_name = scl.segment2
and r.user_table_id = t.user_table_id
and r.business_group_id = P_BUSINESS_GROUP_ID
and a.effective_start_date between r.effective_start_date
and r.effective_end_date
and i.user_row_id = r.user_row_id
and i.user_column_id = c.user_column_id
RESTRICTION.MINIMUM_EMP_CATEGORY_LEVEL[i]
and a.effective_start_date between i.effective_start_date
and
i.effective_end_date
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from hr_soft_coding_keyflex scl4
, per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.soft_coding_keyflex_id = scl4.soft_coding_keyflex_id
RESTRICTION.ASSIGNMENT_START_REASON[scl4]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
--
and exists
(select null
from hr_soft_coding_keyflex scl3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and a3.assignment_type = ''E''
RESTRICTION.PRIMARY_FLAG[a3]
and a3.soft_coding_keyflex_id = scl3.soft_coding_keyflex_id
and not (1=1 RESTRICTION.ASSIGNMENT_START_REASON[scl3])
and scl3.segment3 <> scl.segment3
and a3.effective_end_date
between
P_START_OF_YEAR and a.effective_start_date
and P_END_OF_YEAR >= a3.effective_start_date
and a3.assignment_status_type_id = ast3.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and a3.establishment_id =
v.establishment_id)
');
, (select sum(bal2.run_amount) annual_remuneration
, bal2.assignment_id assignment_id
from per_bf_balances_v bal2
, per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where bal2.assignment_id = a2.assignment_id
and userenv(''lang'')=pt2.language
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and bal2.processing_date between
a2.effective_start_date and a2.effective_end_date
and p2.person_id = a2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and bal2.processing_date between
p2.effective_start_date and p2.effective_end_date
and
bal2.period_start_date >= P_START_OF_YEAR
and bal2.period_end_date <= P_END_OF_YEAR
and bal2.run_amount is not null
RESTRICTION.BALANCE_NAME[bal2]
group by bal2.assignment_id) remuneration
where (p.effective_start_date,a.effective_start_date) =
(select max(p3.effective_start_date),max(a3.effective_start_date)
from per_person_types_tl pt3
, per_all_people_f p3
, P_ESTABLISHMENT_TABLE
, per_assignment_status_types ast3
, per_all_assignments_f a3
where a3.assignment_id = a.assignment_id
and userenv(''lang'')=pt3.language
and P_END_OF_YEAR >= a3.effective_start_date
and P_START_OF_YEAR <= a3.effective_end_date
and P_END_OF_YEAR >= p3.effective_start_date
and P_START_OF_YEAR <= p3.effective_end_date
and a3.establishment_id = v.establishment_id
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and a3.person_id = p3.person_id
and a3.effective_start_date <= p3.effective_end_date
and a3.effective_end_date >= p3.effective_start_date
and p.person_id = p3.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/])
and a.person_id = p.person_id
and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
and remuneration.assignment_id =
a.assignment_id
');
OR exists (select
null
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.person_id = p.person_id
and userenv(''lang'')=pt2.language
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and a2.person_id = p2.person_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.primary_flag = ''Y''
and a2.assignment_type = ''E''
and a2.establishment_id in P_ESTABLISHMENT_LIST
and P_END_OF_YEAR+1 >= a2.effective_start_date
and P_END_OF_YEAR+1 <= a2.effective_end_date
and P_END_OF_YEAR+1 >= p2.effective_start_date
and P_END_OF_YEAR+1 <=
p2.effective_end_date))
');
OR exists (select null
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.person_id = p.person_id
and userenv(''lang'')=pt2.language
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and a2.person_id = p2.person_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.primary_flag = ''Y''
and a2.assignment_type = ''E''
and a2.establishment_id in P_ESTABLISHMENT_LIST
and P_END_OF_YEAR+1 >= a2.effective_start_date
and P_END_OF_YEAR+1 <= a2.effective_end_date
and P_END_OF_YEAR+1 >= p2.effective_start_date
and P_END_OF_YEAR+1 <=
p2.effective_end_date))
');
(select null
from hr_soft_coding_keyflex scl2
, per_person_types_tl pt2
, per_all_people_f p2
,
per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.person_id = p.person_id
and userenv(''lang'')=pt2.language
and a2.soft_coding_keyflex_id = scl2.soft_coding_keyflex_id(+)
and a2.establishment_id in P_ESTABLISHMENT_LIST
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
RESTRICTION.WORK_PATTERN[scl2]
and a2.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and P_END_OF_YEAR+1 <= a2.effective_end_date
and P_END_OF_YEAR+1 >= a2.effective_start_date
and P_END_OF_YEAR+1 <= p2.effective_end_date
and P_END_OF_YEAR+1 >=
p2.effective_start_date))
');
(select null
from hr_soft_coding_keyflex scl2
,
per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
where a2.person_id = p.person_id
and userenv(''lang'')=pt2.language
and a2.soft_coding_keyflex_id = scl2.soft_coding_keyflex_id(+)
and a2.establishment_id in P_ESTABLISHMENT_LIST
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
RESTRICTION.WORK_PATTERN[scl2]
and a2.person_id = p2.person_id
and floor(months_between(P_END_OF_YEAR, p2.date_of_birth)/12) > 50
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and P_END_OF_YEAR+1 <= a2.effective_end_date
and P_END_OF_YEAR+1 >= a2.effective_start_date
and P_END_OF_YEAR+1 <= p2.effective_end_date
and P_END_OF_YEAR+1 >=
p2.effective_start_date))
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
and exists
(select count(*) count
from per_all_people_f p5
, per_all_assignments_f a5
where p5.person_id = p.person_id
and p5.person_id = a5.person_id
and a5.assignment_type = ''E''
and a5.primary_flag = ''Y''
and a5.effective_start_date <= p5.effective_end_date
and p5.effective_start_date <= a5.effective_end_date
and least(p5.effective_end_date,a5.effective_end_date,P_END_OF_YEAR) <=
least(p.effective_end_date,a.effective_end_date,P_END_OF_YEAR)
and least(p5.effective_end_date,a5.effective_end_date,P_END_OF_YEAR) >=
least(p.effective_end_date,a.effective_end_date,P_END_OF_YEAR) -7
having
(min(greatest(p5.effective_start_date,a5.effective_start_date,P_START_OF_YEAR))
<= least(p.effective_end_date,a.effective_end_date,P_END_OF_YEAR) -7)
and count(*) =
(select count(*)
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where p3.person_id = p.person_id
and userenv(''lang'')=pt3.language
and a3.person_id = p3.person_id
and a3.effective_start_date <= p3.effective_end_date
and p3.effective_start_date <= a3.effective_end_date
and least(p3.effective_end_date,a3.effective_end_date,P_END_OF_YEAR) <=
least(p.effective_end_date,a.effective_end_date,P_END_OF_YEAR)
and least(p3.effective_end_date,a3.effective_end_date,P_END_OF_YEAR) >=
least(p.effective_end_date,a.effective_end_date,P_END_OF_YEAR) -7
and a3.establishment_id = v.establishment_id
and a3.assignment_status_type_id =
ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]))
and not exists
(select null
from per_all_assignments_f a4
where a.assignment_id = a4.assignment_id
and a4.primary_flag = ''Y''
and a4.effective_start_date <= least(p.effective_end_date,a.effective_end_date,P_END_OF_YEAR)
and a4.effective_end_date >= least(p.effective_end_date,a.effective_end_date,P_END_OF_YEAR) -7
and not exists
(select null
from per_all_assignments_f a6
where a6.person_id = a4.person_id
and a6.primary_flag = ''Y''
and least(a4.effective_end_date+1,least(p.effective_end_date,a.effective_end_date,P_END_OF_YEAR))
between a6.effective_start_date and
a6.effective_end_date
))
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and per_pos.actual_termination_date between
a2.effective_start_date and a2.effective_end_date
and per_pos.actual_termination_date between
p2.effective_start_date and p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.PRIMARY_FLAG[a2]
RESTRICTION.EMPLOYMENT_CATEGORY[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id =
p.person_id
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a2.assignment_id
and userenv(''lang'')=pt2.language
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.primary_flag = ''Y''
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <=
p2.effective_end_date
and p2.person_id = p.person_id
and p2.effective_start_date <= a2.effective_start_date
and p2.effective_end_date >= a2.effective_start_date
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and p.person_id = pwi.person_id
and pwi.absence_exists_flag = ''Y''
and pwi.incident_date between
P_START_OF_YEAR and
P_END_OF_YEAR
RESTRICTION.WORK_ACCIDENT_TYPE[pwi]
RESTRICTION.WORK_ACCIDENT_CODE[pwi]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id =
v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id =
ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id =
v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select null
from hr_soft_coding_keyflex scl2
,per_cagr_grades_def cagr_def2
,per_person_types_tl pt2
,per_all_people_f p2
,per_periods_of_service pos2
,per_assignment_status_types ast2
,per_all_assignments_f a2
where a2.person_id = p.person_id
and userenv(''lang'')=pt2.language
and a2.establishment_id in P_ESTABLISHMENT_LIST
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
RESTRICTION.PRIMARY_FLAG[a2]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/]
and pos2.period_of_service_id = a2.period_of_service_id
and a2.soft_coding_keyflex_id = scl2.soft_coding_keyflex_id(+)
and a2.cagr_grade_def_id = cagr_def2.cagr_grade_def_id(+)
and P_END_OF_YEAR+1 <= a2.effective_end_date
and P_END_OF_YEAR+1 >=
a2.effective_start_date
and P_END_OF_YEAR+1 <= p2.effective_end_date
and P_END_OF_YEAR+1 >=
p2.effective_start_date))
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
,(select time.run_amount time_amount
,0 weeks_amount
,asg.person_id
,asg.assignment_id
,time.processing_date
from per_bf_balances_v time
,per_all_assignments_f asg
where asg.assignment_id = time.assignment_id
and exists
(select null
from per_person_types_tl pt3
,per_all_people_f p3
,per_assignment_status_types ast3
,per_all_assignments_f a3
,P_ESTABLISHMENT_TABLE
where asg.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and a3.assignment_type = ''E''
and a3.establishment_id = v.establishment_id
and time.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = a3.person_id
and p3.person_type_id=pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and time.processing_date between
p3.effective_start_date and p3.effective_end_date)
RESTRICTION.TIME_BALANCE_NAME[time]
union
select 0 time_amount
,weeks.run_amount weeks_amount
,asg.person_id
,asg.assignment_id
,weeks.processing_date
from per_bf_balances_v weeks
,per_all_assignments_f asg
where asg.assignment_id = weeks.assignment_id
and exists
(select null
from per_person_types_tl pt3
,per_all_people_f p3
,per_assignment_status_types ast3
,per_all_assignments_f a3
,P_ESTABLISHMENT_TABLE
where asg.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and a3.assignment_type = ''E''
and a3.establishment_id = v.establishment_id
and weeks.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = a3.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and weeks.processing_date between
p3.effective_start_date and p3.effective_end_date)
RESTRICTION.WEEKS_WORKED_BALANCE_NAME[weeks]
) balances
where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
and (p.effective_start_date,a.effective_start_date) =
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
,per_all_people_f p2
,per_assignment_status_types ast2
,per_all_assignments_f a2
,P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and balances.assignment_id = a.assignment_id
and balances.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and (balances.time_amount is not null or balances.weeks_amount is not
null)
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a2.assignment_id
and userenv(''lang'')=pt2.language
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.primary_flag = ''Y''
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and p2.person_id = p.person_id
and p2.effective_start_date <= a2.effective_start_date
and p2.effective_end_date >= a2.effective_start_date
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and p.person_id = pwi.person_id
and pwi.absence_exists_flag = ''Y''
and pwi.incident_date between
P_START_OF_YEAR and
P_END_OF_YEAR
RESTRICTION.WORK_ACCIDENT_TYPE[pwi]
RESTRICTION.WORK_ACCIDENT_CODE[pwi]
');
(select count(*) count
from per_all_assignments_f a5
,per_all_people_f p5
where p5.person_id = p.person_id
and p5.person_id = a5.person_id
and a5.assignment_type = ''E''
and a5.primary_flag = ''Y''
and a5.effective_start_date <=
p5.effective_end_date
and p5.effective_start_date <= a5.effective_end_date
and least(p5.effective_end_date,a5.effective_end_date,P_END_OF_YEAR) <=
least(p.effective_end_date,a.effective_end_date,P_END_OF_YEAR)
and least(p5.effective_end_date,a5.effective_end_date,P_END_OF_YEAR)
>= P_START_OF_YEAR
having (min(greatest(p5.effective_start_date,a5.effective_start_date,P_START_OF_YEAR))
<= P_START_OF_YEAR)
and count(*) =
(select count(*)
from hr_soft_coding_keyflex scl3
,per_person_types_tl pt3
,per_all_people_f p3
,per_assignment_status_types ast3
,per_all_assignments_f a3
where p3.person_id = p.person_id
and userenv(''lang'')=pt3.language
and a3.person_id = p3.person_id
and a3.effective_start_date <= p3.effective_end_date
and p3.effective_start_date <= a3.effective_end_date
and least(p3.effective_end_date,a3.effective_end_date,P_END_OF_YEAR) <=
least(p.effective_end_date,a.effective_end_date,P_END_OF_YEAR)
and least(p3.effective_end_date,a3.effective_end_date,P_END_OF_YEAR)
>= P_START_OF_YEAR
and a3.establishment_id in P_ESTABLISHMENT_LIST
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.primary_flag = ''Y''
RESTRICTION.EMPLOYMENT_CATEGORY[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and p3.person_type_id = pt3.person_type_id
and a3.soft_coding_keyflex_id = scl3.soft_coding_keyflex_id(+)
RESTRICTION.WORK_PATTERN[scl3]
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]))
and not exists
(select null
from per_all_assignments_f a4
where a.assignment_id = a4.assignment_id
and a4.primary_flag = ''Y''
and a4.effective_start_date <= P_END_OF_YEAR
and a4.effective_end_date >= P_START_OF_YEAR
and not exists
(select null
from per_all_assignments_f a6
where a6.person_id = a4.person_id
and a6.primary_flag = ''Y''
and least(a4.effective_end_date+1,P_END_OF_YEAR) between a6.effective_start_date and
a6.effective_end_date
))
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a2.assignment_id
and userenv(''lang'')=pt2.language
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.primary_flag = ''Y''
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
and P_END_OF_YEAR >= a2.effective_start_date
and P_START_OF_YEAR <= a2.effective_end_date
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <=
p2.effective_end_date
and p2.person_id = p.person_id
and p2.effective_start_date <= a2.effective_start_date
and p2.effective_end_date >= a2.effective_start_date
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and p.person_id = pwi.person_id
and pwi.incident_date between
P_START_OF_YEAR and
P_END_OF_YEAR
RESTRICTION.WORK_ACCIDENT_TYPE[pwi]
RESTRICTION.WORK_ACCIDENT_CODE[pwi]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a2.assignment_id
and userenv(''lang'')=pt2.language
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.primary_flag = ''Y''
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
and a2.effective_start_date <= P_END_OF_YEAR
and a2.effective_end_date >= P_START_OF_YEAR
and P_END_OF_YEAR >= p2.effective_start_date
and P_START_OF_YEAR <= p2.effective_end_date
and p2.person_id = p.person_id
and p2.effective_start_date <= a2.effective_start_date
and p2.effective_end_date >= a2.effective_start_date
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and p.person_id = pwi.person_id
and pwi.absence_exists_flag = ''Y''
and pwi.incident_date between
P_START_OF_YEAR and
P_END_OF_YEAR
RESTRICTION.WORK_ACCIDENT_TYPE[pwi]
RESTRICTION.WORK_ACCIDENT_CODE[pwi]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a2.assignment_id
and userenv(''lang'')=pt2.language
and a2.assignment_status_type_id = ast2.assignment_status_type_id
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
RESTRICTION.PRIMARY_FLAG[a2]
and a2.assignment_type = ''E''
and a2.establishment_id = v.establishment_id
and a2.effective_start_date <= P_END_OF_YEAR
and a2.effective_end_date >= P_START_OF_YEAR
and p2.effective_start_date <= P_END_OF_YEAR
and p2.effective_end_date >= P_START_OF_YEAR
and p2.person_id = p.person_id
and p2.effective_start_date <= a2.effective_start_date
and p2.effective_end_date >= a2.effective_start_date
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and bal.assignment_id = a.assignment_id
and bal.processing_date
between P_START_OF_YEAR and P_END_OF_YEAR
and bal.run_amount is not null
and exists
(select null
from per_person_types_tl pt3
, per_all_people_f p3
, per_assignment_status_types ast3
, per_all_assignments_f a3
, P_ESTABLISHMENT_TABLE
where a.assignment_id = a3.assignment_id
and userenv(''lang'')=pt3.language
and a3.assignment_status_type_id = ast3.assignment_status_type_id
and a3.assignment_type = ''E''
and a3.establishment_id = v.establishment_id
RESTRICTION.PRIMARY_FLAG[a3]
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast3]
and bal.processing_date between
a3.effective_start_date and a3.effective_end_date
and p3.person_id = p.person_id
and p3.person_type_id = pt3.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p3*/]
and bal.processing_date between
p3.effective_start_date
and
p3.effective_end_date)
RESTRICTION.BALANCE_NAME[bal]
');
(select max(p2.effective_start_date),max(a2.effective_start_date)
from per_person_types_tl pt2
, per_all_people_f p2
, per_assignment_status_types ast2
, per_all_assignments_f a2
, P_ESTABLISHMENT_TABLE
where a2.assignment_id = a.assignment_id
and userenv(''lang'')=pt2.language
and to_date(P_YEAR||''0331'',''YYYYMMDD'') >= a2.effective_start_date
and to_date(P_YEAR||''0331'',''YYYYMMDD'') <= a2.effective_end_date
and to_date(P_YEAR||''0331'',''YYYYMMDD'') >= p2.effective_start_date
and to_date(P_YEAR||''0331'',''YYYYMMDD'') <= p2.effective_end_date
and a2.establishment_id = a.establishment_id
and a2.establishment_id = v.establishment_id
and a2.assignment_status_type_id = ast2.assignment_status_type_id
and a2.assignment_type = ''E''
and a2.primary_flag = ''Y''
RESTRICTION.ASSIGNMENT_STATUS_TYPE[ast2]
and a2.person_id = p2.person_id
and a2.effective_start_date <= p2.effective_end_date
and a2.effective_end_date >= p2.effective_start_date
and p.person_id = p2.person_id
and p2.person_type_id = pt2.person_type_id
RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
and a.person_id = p.person_id
and exists
(select null
from per_disabilities_f pdf
where p.person_id = pdf.person_id
RESTRICTION.DISABILITY_REASON[pdf]-- Work Related Disability found from reason
and to_date(P_YEAR||''0331'',''YYYYMMDD'') between
pdf.effective_start_date and pdf.effective_end_date
RESTRICTION.COTOREP_CLASS[pdf]
RESTRICTION.DISABILITY_RATE[pdf]
and exists
(select null
from per_all_assignments_f a3,
P_ESTABLISHMENT_TABLE
where a3.person_id = p.person_id
and pdf.effective_start_date
between a3.effective_start_date and a3.effective_end_date
and a3.establishment_id = v.establishment_id
and a3.primary_flag = ''Y'')
)
');
,p_restriction_sql => 'select t.user_person_type VALUE
,t.user_person_type MEANING
from per_person_types_tl t,
per_person_types b
where b.business_group_id=:ctl_globals.business_group_id
and b.person_type_id=t.person_type_id
and userenv(''lang'')=t.language');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING from hr_lookups
where lookup_type = ''FR_STARTING_REASON''');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING from hr_lookups
where lookup_type = ''PER_ASS_SYS_STATUS''
order by lookup_code');
,p_restriction_sql => 'select displayed_name VALUE
, displayed_name MEANING
from per_bf_balance_types
where business_group_id = :ctl_globals.business_group_id');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_lookups
where lookup_type = ''FR_CONTRACT_CATEGORY''');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_lookups
where lookup_type = ''CONTRACT_TYPE''
order by lookup_code');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_leg_lookups
where lookup_type = ''DISABILITY_CATEGORY''');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_leg_lookups
where lookup_type = ''DISABILITY_REASON''');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_leg_lookups
where lookup_type = ''EMP_CAT''
order by lookup_code');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_leg_lookups
where lookup_type = ''CONSULTATION_RESULT''');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_leg_lookups
where lookup_type = ''CONSULTATION_TYPE''');
,p_restriction_sql => 'select lookup_code VALUE , meaning MEANING
from hr_lookups
where lookup_type = ''YES_NO''
order by lookup_code');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_lookups
where lookup_type = ''FR_REPRESENTATIVE_TYPE''');
,p_restriction_sql => 'select displayed_name VALUE
, displayed_name MEANING
from per_bf_balance_types
where business_group_id = :ctl_globals.business_group_id');
,p_restriction_sql => 'select t.user_person_type VALUE
,t.user_person_type MEANING
from per_person_types_tl t,
per_person_types b
where b.business_group_id=:ctl_globals.business_group_id
and b.person_type_id=t.person_type_id
and userenv(''lang'')=t.language');
,p_restriction_sql => 'select displayed_name VALUE
, displayed_name MEANING
from per_bf_balance_types
where business_group_id = :ctl_globals.business_group_id');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_leg_lookups
where lookup_type = ''HAZARD_TYPE''');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_leg_lookups
where lookup_type = ''INCIDENT_TYPE''
order by lookup_code');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_leg_lookups
where lookup_type = ''AT_WORK_FLAG''');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING
from hr_lookups
where lookup_type = ''FR_WORK_PATTERN''');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING from hr_leg_lookups
where lookup_type = ''FR_ENDING_REASON''');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING from hr_leg_lookups
where lookup_type = ''LEAV_REAS''');
,p_restriction_sql => 'select lookup_code VALUE
, meaning MEANING from hr_lookups
where lookup_type = ''ABSENCE_CATEGORY''');
Update hr_summary
set text_value1 = l_template_name
where type = 'TEMPLATE'
and text_value1 = 'Example Template'
and text_value7 = 'Y' /* Only update Seeded Data */
and business_group_id = p_business_group_id;
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_STARTING_REASON','MMO_STARTING_CATEGORY','CHANGE_COMPANY','TR');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_STARTING_REASON','MMO_STARTING_CATEGORY','CHANGE_ESTABLISHMENT','TR');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_STARTING_REASON','MMO_STARTING_CATEGORY','CHANGE_OF_JOB','TR');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_STARTING_REASON','MMO_STARTING_CATEGORY','HIRED_FIXED_TERM','RD');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_STARTING_REASON','MMO_STARTING_CATEGORY','HIRED_PERMANENT','RI');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_STARTING_REASON','MMO_STARTING_CATEGORY','OTHER','AU');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_STARTING_REASON','MMO_STARTING_CATEGORY','REHIRED_FIXED_TERM','RI');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_STARTING_REASON','MMO_STARTING_CATEGORY','REHIRED_PERMANENT','RD');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_STARTING_REASON','MMO_STARTING_CATEGORY','RECLASS','AU');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_STARTING_REASON','MMO_STARTING_CATEGORY','TEMPORARY_REPLACEMENT','AU');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','CHANGE_COMPANY','TR');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','CHANGE_ESTABLISHMENT','TR');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','CHANGE_OF_JOB','DM');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','DEATH','AU');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','ECONOMIC_REDUNDANCY','LA');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','END_FIXED_TERM','FD');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','END_PROBATION_PERIOD','ED');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','NATIONAL_SERVICE','SN');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','OTHER','AU');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','REDUNDANCY','LA');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','REPLACEMENT','AU');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','RESIGNATION','DM');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','RETIREMENT','RT');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','EARLY_RETIREMENT','RT');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','VOLUNTARY_RETIREMENT','RT');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','MUTUAL_AGREEMENT_BREACH','AU');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','G','LA');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','SERIOUS_MISCONDUCT','LA');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','PHYSICAL_INABILITY','LA');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','PHYSICAL_INABILITY_WA','LA');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','R','RT');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ENDING_REASON','MMO_ENDING_CATEGORY','D','AU');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','3862','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','3863','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','4802','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','5223','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','5315','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','5317','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','5441','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','5445','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','5510','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6234','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6241','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6242','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6243','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6244','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6245','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6246','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6331','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6341','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6343','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6411','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6412','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6415','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6511','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6512','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6513','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6522','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6531','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6532','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6741','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6742','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6841','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6911','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_PCS_CODE','FR_D2_PARTICULAR','6921','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'EMP_CAT','FR_D2_CATEGORY','FR','F');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'EMP_CAT','FR_D2_CATEGORY','FT','F');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'EMP_CAT','FR_D2_CATEGORY','PR','P');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'EMP_CAT','FR_D2_CATEGORY','PT','P');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','389B','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','389C','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','480B','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','526E','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','533A','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','533B','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','534A','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','534B','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','546A','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','546D','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','546E','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','553B','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','624D','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','621A','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','621B','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','621C','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','621E','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','621G','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','632A','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','632C','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','632E','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','641A','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','641B','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','643A','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','651A','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','651B','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','652B','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','654B','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','654C','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','656B','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','656C','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','671C','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','671D','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','681A','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','691A','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_NEW_PCS_CODE','FR_D2_PARTICULAR','692A','Y');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ABS_CATG_ADS_ABS_CODE','ADS_ABSENCE_CODE', 'FR_MAIN_HOLIDAY','COP');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ABS_CATG_ADS_ABS_CODE','ADS_ABSENCE_CODE', 'FR_RTT_HOLIDAY','COP');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ABS_CATG_ADS_ABS_CODE','ADS_ABSENCE_CODE', 'FR_ADDITIONAL_HOLIDAY','COP');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ABS_CATG_ADS_ABS_CODE','ADS_ABSENCE_CODE', 'HOLS', 'COP');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ABS_CATG_ADS_ABS_CODE','ADS_ABSENCE_CODE','S','MAL');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ABS_CATG_ADS_ABS_CODE','ADS_ABSENCE_CODE','M','MAT');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ABS_CATG_ADS_ABS_CODE','ADS_ABSENCE_CODE','PL','PAT'); --Check with paternity designs
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_WORK_INC_ADS_ABS_CODE','ADS_ABSENCE_CODE', 'O','MP');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_WORK_INC_ADS_ABS_CODE','ADS_ABSENCE_CODE', 'R','AT');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_WORK_INC_ADS_ABS_CODE','ADS_ABSENCE_CODE', 'W','WT');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ABS_CATG_ADS_STATUS_CODE','ADS_STATUS', 'FR_MAIN_HOLIDAY','FR_PAID_HOLIDAY');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ABS_CATG_ADS_STATUS_CODE','ADS_STATUS', 'FR_ADDITIONAL_HOLIDAY','FR_PAID_HOLIDAY');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ABS_CATG_ADS_STATUS_CODE','ADS_STATUS', 'FR_RTT_HOLIDAY','FR_PAID_HOLIDAY');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_ABS_CATG_ADS_STATUS_CODE','ADS_STATUS', 'HOLS','FR_UNPAID_HOLIDAY');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_TERM_REASON_ADS_STATUS_CODE','ADS_STATUS', 'DISMISS_CODE1','FR_DISMISSAL');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_TERM_REASON_ADS_STATUS_CODE','ADS_STATUS', 'DISMISS_CODE2','FR_DISMISSAL');
per_alt_lookups_pkg.update_instance_value(p_business_group_id,lc_legislation_code,'FR_TERM_REASON_ADS_STATUS_CODE','ADS_STATUS', 'RESIGNATION','FR_RESIGNATION');