ENGINATICSSYSTEM_ADMINISTRATORSYSADMINE811F942597A2075E0530100007FF4F2Blitz Upload ModeYselect xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all
select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual
US8E2FF36EDE9B79D2E0530100007F1FF2PER Business GroupYselect
pbg.name value,
hla.location_code description
from
per_business_groups pbg,
hr_locations_all hla
where
pbg.location_id=hla.location_id(+)
order by
pbg.nameUSFFD22893B7602CC3E0530100007F4775with input_values as
(select * from
(
select peevf.element_entry_id,pivfv.name,pivfv.input_value_id,peevf.screen_entry_value entry_value, row_number() over (partition by pivfv.element_type_id,peevf.element_entry_id order by pivfv.display_sequence) rnum from
pay_input_values_f_vl pivfv,
pay_element_entry_values_f peevf
where
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between peevf.effective_start_date and peevf.effective_end_date and
pivfv.input_value_id=peevf.input_value_id
)
pivot (
max(name) value_name, max(input_value_id) value_id,max(entry_value) entry_value
for rnum in
(
1 input1, 2 input2, 3 input3, 4 input4, 5 input5,6 input6, 7 input7, 8 input8, 9 input9, 10 input10, 11 input11, 12 input12, 13 input13, 14 input14, 15 input15
)
)
)
select
null action_,
null status_,
null message_,
null modified_columns_,
pbg.name business_group,
papf.full_name employee_name,
papf.employee_number,
petfv.element_name,
peef.element_entry_id,
hl.meaning entry_type,
null delete_element_entry,
null datetrack_update_mode,
peef.object_version_number,
to_date(null) effective_date,
peef.effective_start_date effective_start_date,
peef.effective_end_date effective_end_date,
input1_value_name,
input1_entry_value,
input2_value_name,
input2_entry_value,
input3_value_name,
input3_entry_value,
input4_value_name,
input4_entry_value,
input5_value_name,
input5_entry_value,
input6_value_name,
input6_entry_value,
input7_value_name,
input7_entry_value,
input8_value_name,
input8_entry_value,
input9_value_name,
input9_entry_value,
input10_value_name,
input10_entry_value,
input11_value_name,
input11_entry_value,
input12_value_name,
input12_entry_value,
input13_value_name,
input13_entry_value,
input14_value_name,
input14_entry_value,
input15_value_name,
input15_entry_value
from
per_all_people_f papf,
per_all_assignments_f paaf,
pay_element_entries_f peef,
per_business_groups pbg,
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_assignment_status_types past,
hr_lookups hl,
input_values iv
where
1=1 and
pbg.business_group_id=papf.business_group_id and
papf.person_id=paaf.person_id and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and
trunc(sysdate) between peef.effective_start_date and peef.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
pelf.element_type_id=peef.element_type_id and
petfv.element_type_id=peef.element_type_id and
pelf.business_group_id=pbg.business_group_id and
paaf.assignment_id=peef.assignment_id and
peef.creator_type<>'UT' and
peef.element_link_id=pelf.element_link_id and
iv.element_entry_id=peef.element_entry_id and
hl.lookup_type='ENTRY_TYPE' and
hl.lookup_code=peef.entry_type and
paaf.assignment_type='E' and
paaf.primary_flag='Y' and
paaf.assignment_status_type_id=past.assignment_status_type_id and
past.per_system_status='ACTIVE_ASSIGN' and
not exists
(
select 1
from
per_periods_of_service ppos
where
ppos.person_id=papf.person_id and
ppos.period_of_service_id=(select max(period_of_service_id) from per_periods_of_service where person_id = papf.person_id) and
ppos.actual_termination_date < trunc(sysdate)
)
¬_use_first_block
&report_table_select &report_table_name &report_table_where_clause
&processed_run
order by 6,9YUAxxen_hr_upload.create_update_py_elmnt_entriesUSPER Payroll Element Entry UploadPER Payroll Element Entry Upload
==================
Use this upload to upload new or update existing PER Payroll Element Entries, or to enter adjustment entries.EnginaticsR12 onlyUpload¬_use_first_block&processed_run&report_table_name&report_table_select&report_table_where_clause1=11101=1:upload_mode like '%' || xxen_upload.action_updateLOVBlitz Upload ModeE811F942597A2075E0530100007FF4F2Yselect xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all
select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual
YUSUpload Mode2201=1pbg.name=:business_groupLOVPER Business Group8E2FF36EDE9B79D2E0530100007F1FF2Yselect
pbg.name value,
hla.location_code description
from
per_business_groups pbg,
hr_locations_all hla
where
pbg.location_id=hla.location_id(+)
order by
pbg.nameYUSBusiness Group3301=1papf.full_name=:employee_nameLOV customYselect
papf.full_name value,
papf.global_name description
from
per_all_People_f papf,
per_all_assignments_f paaf,
per_business_groups pbg
where paaf.business_group_id=pbg.business_group_id and
pbg.name=:$flex$.business_group and
papf.person_id=paaf.person_id and
trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
paaf.assignment_type='E' and
:$flex$.upload_mode like '%' || xxen_upload.action_update and
not exists
(
select 1
from
apps.per_periods_of_service ppos
where
ppos.person_id=papf.person_id and
ppos.period_of_service_id=(select max(period_of_service_id) from per_periods_of_service where person_id = papf.person_id) and
ppos.actual_termination_date < trunc(sysdate)
)USEmployee Name:$flex$.business_groupBusiness GroupEmployee Name:$flex$.upload_modeUpload ModeEmployee Name1ACTION_Charvarchar22STATUS_Charvarchar23MESSAGE_Charvarchar24MODIFIED_COLUMNS_Charvarchar2Y5BUSINESS_GROUPLOVvarchar2PER Business Group8E2FF36EDE9B79D2E0530100007F1FF2Yselect
pbg.name value,
hla.location_code description
from
per_business_groups pbg,
hr_locations_all hla
where
pbg.location_id=hla.location_id(+)
order by
pbg.nameselect
pbg.business_group_id id,
pbg.name value
from
per_business_groups pbgYY6EMPLOYEE_NAMELOV customvarchar2Yselect
papf.full_name value,
papf.global_name description
from
per_all_people_f papf,
per_all_assignments_f paaf,
per_business_groups pbg
where
pbg.name=:$column$.business_group and
paaf.business_group_id=pbg.business_group_id and
papf.person_id=paaf.person_id and
trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
paaf.assignment_type='E' and
not exists
(
select 1
from
apps.per_periods_of_service ppos
where
ppos.person_id=papf.person_id and
ppos.period_of_service_id=(select max(period_of_service_id) from per_periods_of_service where person_id = papf.person_id) and
ppos.actual_termination_date < trunc(sysdate)
)select
paaf.assignment_id id,
papf.full_name value
from
per_all_people_f papf,
per_all_assignments_f paaf,
per_business_groups pbg,
per_assignment_status_types past
where
pbg.name=:$column$.business_group and
paaf.business_group_id=pbg.business_group_id and
papf.full_name=:$column$.employee_name and
papf.person_id=paaf.person_id and
trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
paaf.primary_flag='Y' and
paaf.assignment_type ='E' and
paaf.assignment_status_type_id=past.assignment_status_type_id and
past.per_system_status='ACTIVE_ASSIGN' and
not exists
(
select 1
from
per_periods_of_service ppos
where
ppos.person_id=papf.person_id and
ppos.period_of_service_id=(select max(period_of_service_id) from per_periods_of_service where person_id = papf.person_id) and
ppos.actual_termination_date < trunc(sysdate)
)YY7EMPLOYEE_NUMBERCharvarchar2select
papf.employee_number
from
per_all_People_f papf,
per_all_assignments_f paaf,
per_business_groups pbg
where
pbg.name=:$column$.business_group and
paaf.business_group_id=pbg.business_group_id and
papf.full_name=:$column$.employee_name and
papf.person_id=paaf.person_id and
trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
paaf.assignment_type='E' and
not exists
(
select 1
from
apps.per_periods_of_service ppos
where
ppos.person_id=papf.person_id and
ppos.period_of_service_id=(select max(period_of_service_id) from per_periods_of_service where person_id = papf.person_id) and
ppos.actual_termination_date < trunc(sysdate)
)Y8ELEMENT_NAMELOV customvarchar2Yselect
petfv.element_name value,
petfv.description
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg
where
pbg.name=:$column$.business_group and
pelf.payroll_id is null and
pelf.business_group_id=pbg.business_group_id and
pelf.element_type_id=petfv.element_type_id and
petfv.processing_type='R' and
--petfv.once_each_period_flag is null and
--petfv.post_termination_rule<>'A' and
petfv.process_in_run_flag='Y' and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
union
select
petfv.element_name value,
petfv.description
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
per_all_people_f papf,
per_all_assignments_f paaf
where
pbg.name=:$column$.business_group and
pelf.payroll_id=paaf.payroll_id and
pelf.business_group_id=pbg.business_group_id and
pelf.element_type_id=petfv.element_type_id and
petfv.processing_type='R' and
--petfv.once_each_period_flag is null and
petfv.process_in_run_flag='Y' and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
paaf.business_group_id=pbg.business_group_id and
papf.full_name=:$column$.employee_name and
papf.person_id=paaf.person_id and
trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
paaf.assignment_type='E' and
not exists
(
select 1
from
apps.per_periods_of_service ppos
where
ppos.person_id=papf.person_id and
ppos.period_of_service_id=(select max(period_of_service_id) from per_periods_of_service where person_id = papf.person_id) and
ppos.actual_termination_date < trunc(sysdate)
)select
pelf.element_link_id id,
petfv.element_name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
pelf.element_type_id=petfv.element_type_id and
petfv.processing_type='R' and
--petfv.once_each_period_flag is null and
petfv.process_in_run_flag='Y' and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateYY9ELEMENT_ENTRY_IDNumbernumberY10ENTRY_TYPELOV customvarchar2Yselect
hl.lookup_code id,
hl.meaning value,
hl.description
from
hr_lookups hl
where
hl.lookup_type='ENTRY_TYPE' and
hl.lookup_code in ('A','E')select
hl.lookup_code id,
hl.meaning value,
hl.description
from
hr_lookups hl
where
hl.lookup_type='ENTRY_TYPE' and
hl.lookup_code in ('A','E')xxen_util.meaning('E','ENTRY_TYPE',3)Y11DELETE_ELEMENT_ENTRYLOV Oraclevarchar2Yes_NoYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,descriptionChoose 'Yes' if the entry needs to be deleted. Appropriate Datetrack Update Mode needs to be selected as well.12DATETRACK_UPDATE_MODELOV customvarchar2Yselect
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl
where
hl.lookup_type='EVENT_TYPE' and
hl.lookup_code in ('CORRECTION','UPDATE','UPDATE_OVERRIDE','UPDATE_CHANGE_INSERT') and
nvl(:$column$.delete_element_entry,xxen_util.meaning('N','YES_NO',0))=xxen_util.meaning('N','YES_NO',0)
union all
select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl
where
hl.lookup_type='EVENT_TYPE' and
hl.lookup_code in ('FUTURE_CHANGE','DELETE','DELETE_NEXT_CHANGE','ZAP') and
nvl(:$column$.delete_element_entry,xxen_util.meaning('N','YES_NO',0))=xxen_util.meaning('Y','YES_NO',0)Required to update or delete the entry13OBJECT_VERSION_NUMBERNumbernumberYY14EFFECTIVE_DATEDatedatesysdateY15EFFECTIVE_START_DATEDatedate16EFFECTIVE_END_DATEDatedate17INPUT1_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=1select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input1_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=1Y18INPUT1_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=1) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input1_value_name19INPUT2_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=2select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input2_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=2Y20INPUT2_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=2) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input2_value_name21INPUT3_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=3select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input3_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=3Y22INPUT3_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=3) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input3_value_name23INPUT4_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=4select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input4_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=4Y24INPUT4_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=4) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input4_value_name25INPUT5_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=5select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input5_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=5Y26INPUT5_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=5) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input5_value_name27INPUT6_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=6select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input6_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=6Y28INPUT6_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=6) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input6_value_name29INPUT7_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=7select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input7_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=7Y30INPUT7_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=7) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input7_value_name31INPUT8_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=8select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input8_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=8Y32INPUT8_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=8) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input8_value_name33INPUT9_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=9select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input9_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=9Y34INPUT9_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=9) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input9_value_name35INPUT10_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=10select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input10_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=10Y36INPUT10_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=10) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input10_value_name37INPUT11_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=11select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input11_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=11Y38INPUT11_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=11) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input11_value_name39INPUT12_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=12select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input12_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=12Y40INPUT12_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=12) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input12_value_name41INPUT13_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=13select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input13_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=13Y42INPUT13_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=13) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input13_value_name43INPUT14_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=14select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input14_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=14Y44INPUT14_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=14) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input14_value_name45INPUT15_VALUE_NAMELOV customvarchar2Yselect
y.name value,
null description
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=15select
pivfv.input_value_id id,
pivfv.name value
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pivfv.name=:$column$.input15_value_name and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_dateselect
y.name
from
(
select
pivfv.name,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=15Y46INPUT15_ENTRY_VALUELOV customvarchar2select
hl.lookup_code value,
hl.meaning description
from
hr_lookups hl,
(select y.*
from
(
select
pivfv.name,
pivfv.lookup_type,
row_number() over (partition by 1 order by pivfv.display_sequence) seq
from
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_business_groups pbg,
pay_input_values_f_vl pivfv
where
pbg.name=:$column$.business_group and
pelf.business_group_id=pbg.business_group_id and
petfv.element_name=:$column$.element_name and
pelf.element_type_id=petfv.element_type_id and
pelf.element_type_id=pivfv.element_type_id and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between pivfv.effective_start_date and pivfv.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date
)y
where y.seq=15) pivf
where
hl.lookup_type=pivf.lookup_type and
pivf.lookup_type is not null and
pivf.name=:$column$.input15_value_nameU1action_inCharACTION_CODE_U2p_effective_dateinDateEFFECTIVE_DATEU3p_business_group_idinNumberBUSINESS_GROUPU4p_assignment_idinNumberEMPLOYEE_NAMEU5p_element_link_idinNumberELEMENT_NAMEU6p_entry_typeinCharENTRY_TYPEU10p_element_entry_idin/outNumberELEMENT_ENTRY_IDU11p_input_value_id1inNumberYINPUT1_VALUE_NAMEU12p_input_value_id2inNumberYINPUT2_VALUE_NAMEU13p_input_value_id3inNumberYINPUT3_VALUE_NAMEU14p_input_value_id4inNumberYINPUT4_VALUE_NAMEU15p_input_value_id5inNumberYINPUT5_VALUE_NAMEU16p_input_value_id6inNumberYINPUT6_VALUE_NAMEU17p_input_value_id7inNumberYINPUT7_VALUE_NAMEU18p_input_value_id8inNumberYINPUT8_VALUE_NAMEU19p_input_value_id9inNumberYINPUT9_VALUE_NAMEU20p_input_value_id10inNumberYINPUT10_VALUE_NAMEU21p_input_value_id11inNumberYINPUT11_VALUE_NAMEU22p_input_value_id12inNumberYINPUT12_VALUE_NAMEU23p_input_value_id13inNumberYINPUT13_VALUE_NAMEU24p_input_value_id14inNumberYINPUT14_VALUE_NAMEU25p_input_value_id15inNumberYINPUT15_VALUE_NAMEU26p_entry_value1inCharYINPUT1_ENTRY_VALUEU27p_entry_value2inCharYINPUT2_ENTRY_VALUEU28p_entry_value3inCharYINPUT3_ENTRY_VALUEU29p_entry_value4inCharYINPUT4_ENTRY_VALUEU30p_entry_value5inCharYINPUT5_ENTRY_VALUEU31p_entry_value6inCharYINPUT6_ENTRY_VALUEU32p_entry_value7inCharYINPUT7_ENTRY_VALUEU33p_entry_value8inCharYINPUT8_ENTRY_VALUEU34p_entry_value9inCharYINPUT9_ENTRY_VALUEU35p_entry_value10inCharYINPUT10_ENTRY_VALUEU36p_entry_value11inCharYINPUT11_ENTRY_VALUEU37p_entry_value12inCharYINPUT12_ENTRY_VALUEU38p_entry_value13inCharYINPUT13_ENTRY_VALUEU39p_entry_value14inCharYINPUT14_ENTRY_VALUEU40p_entry_value15inCharYINPUT15_ENTRY_VALUEU41p_datetrack_update_modeinCharYDATETRACK_UPDATE_MODEU42p_object_version_numberinNumberYOBJECT_VERSION_NUMBERU43p_delete_element_entryinCharDELETE_ELEMENT_ENTRYU44status_outCharSTATUS_CODE_U45message_outCharMESSAGE_Lexical¬_use_first_blockand 1=0Lexical&processed_runand 'Y'='Y'Lexical&report_table_namexxen_per_payroll_elemen_0730_uLexical&report_table_selectunion all
select
xm.action_,
xm.status_,
xm.message_,
xm.modified_columns_,
nvl(pa.business_group,xm.business_group) business_group,
nvl(pa.employee_name,xm.employee_name) employee_name,
nvl(pa.employee_number,xm.employee_number) employee_number,
nvl(pa.element_name,xm.element_name) element_name,
nvl(pa.element_entry_id,xm.element_entry_id) element_entry_id,
nvl(pa.entry_type,xm.entry_type) entry_type,
xm.delete_element_entry,
case when xm.status_code_=xxen_upload.status_success then null else xm.datetrack_update_mode end datetrack_update_mode,
nvl(pa.object_version_number,xm.object_version_number) object_version_number,
case when xm.status_code_=xxen_upload.status_success then to_date(null) else xm.effective_date end effective_date,
nvl(pa.effective_start_date,xm.effective_start_date) effective_start_date,
nvl(pa.effective_end_date,xm.effective_end_date) effective_end_date,
nvl(pa.input1_value_name,xm.input1_value_name) input1_value_name,
nvl(pa.input1_entry_value,xm.input1_entry_value) input1_entry_value,
nvl(pa.input2_value_name,xm.input2_value_name) input2_value_name,
nvl(pa.input2_entry_value,xm.input2_entry_value) input2_entry_value,
nvl(pa.input3_value_name,xm.input3_value_name) input3_value_name,
nvl(pa.input3_entry_value,xm.input3_entry_value) input3_entry_value,
nvl(pa.input4_value_name,xm.input4_value_name) input4_value_name,
nvl(pa.input4_entry_value,xm.input4_entry_value) input4_entry_value,
nvl(pa.input5_value_name,xm.input5_value_name) input5_value_name,
nvl(pa.input5_entry_value,xm.input5_entry_value) input5_entry_value,
nvl(pa.input6_value_name,xm.input6_value_name) input6_value_name,
nvl(pa.input6_entry_value,xm.input6_entry_value) input6_entry_value,
nvl(pa.input7_value_name,xm.input7_value_name) input7_value_name,
nvl(pa.input7_entry_value,xm.input7_entry_value) input7_entry_value,
nvl(pa.input8_value_name,xm.input8_value_name) input8_value_name,
nvl(pa.input8_entry_value,xm.input8_entry_value) input8_entry_value,
nvl(pa.input9_value_name,xm.input9_value_name) input9_value_name,
nvl(pa.input9_entry_value,xm.input9_entry_value) input9_entry_value,
nvl(pa.input10_value_name,xm.input10_value_name) input10_value_name,
nvl(pa.input10_entry_value,xm.input10_entry_value) input10_entry_value,
nvl(pa.input11_value_name,xm.input11_value_name) input11_value_name,
nvl(pa.input11_entry_value,xm.input11_entry_value) input11_entry_value,
nvl(pa.input12_value_name,xm.input12_value_name) input12_value_name,
nvl(pa.input12_entry_value,xm.input12_entry_value) input12_entry_value,
nvl(pa.input13_value_name,xm.input13_value_name) input13_value_name,
nvl(pa.input13_entry_value,xm.input13_entry_value) input13_entry_value,
nvl(pa.input14_value_name,xm.input14_value_name) input14_value_name,
nvl(pa.input14_entry_value,xm.input14_entry_value) input14_entry_value,
nvl(pa.input15_value_name,xm.input15_value_name) input15_value_name,
nvl(pa.input15_entry_value,xm.input15_entry_value) input15_entry_value
from
(
select
pbg.name business_group,
pbg.business_group_id,
papf.full_name employee_name,
papf.employee_number,
paaf.assignment_id,
petfv.element_name,
pelf.element_link_id,
peef.element_entry_id,
hl.meaning entry_type,
null delete_element_entry,
null datetrack_update_mode,
peef.object_version_number,
peef.effective_start_date,
peef.effective_end_date,
input1_value_name,
input1_value_id,
input1_entry_value,
input2_value_name,
input2_value_id,
input2_entry_value,
input3_value_name,
input3_value_id,
input3_entry_value,
input4_value_name,
input4_value_id,
input4_entry_value,
input5_value_name,
input5_value_id,
input5_entry_value,
input6_value_name,
input6_value_id,
input6_entry_value,
input7_value_name,
input7_value_id,
input7_entry_value,
input8_value_name,
input8_value_id,
input8_entry_value,
input9_value_name,
input9_value_id,
input9_entry_value,
input10_value_name,
input10_value_id,
input10_entry_value,
input11_value_name,
input11_value_id,
input11_entry_value,
input12_value_name,
input12_value_id,
input12_entry_value,
input13_value_name,
input13_value_id,
input13_entry_value,
input14_value_name,
input14_value_id,
input14_entry_value,
input15_value_name,
input15_value_id,
input15_entry_value
from
per_all_people_f papf,
per_all_assignments_f paaf,
pay_element_entries_f peef,
per_business_groups pbg,
pay_element_links_f pelf,
pay_element_types_f_vl petfv,
per_assignment_status_types past,
hr_lookups hl,
input_values iv
where
1=1 and
pbg.business_group_id=papf.business_group_id and
papf.person_id=paaf.person_id and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and
trunc(sysdate) between peef.effective_start_date and peef.effective_end_date and
trunc(sysdate) between pelf.effective_start_date and pelf.effective_end_date and
trunc(sysdate) between petfv.effective_start_date and petfv.effective_end_date and
hl.lookup_type='ENTRY_TYPE' and
hl.lookup_code=peef.entry_type and
pelf.element_type_id=peef.element_type_id and
petfv.element_type_id=peef.element_type_id and
pelf.business_group_id=pbg.business_group_id and
paaf.assignment_id=peef.assignment_id and
peef.creator_type<>'UT' and
peef.element_link_id=pelf.element_link_id and
iv.element_entry_id=peef.element_entry_id and
paaf.assignment_type='E' and
paaf.primary_flag='Y' and
paaf.assignment_status_type_id=past.assignment_status_type_id and
past.per_system_status='ACTIVE_ASSIGN' and
not exists
(
select 1
from
apps.per_periods_of_service ppos
where
ppos.person_id=papf.person_id and
ppos.period_of_service_id=(select max(period_of_service_id) from per_periods_of_service where person_id = papf.person_id) and
ppos.actual_termination_date < trunc(sysdate)
)
) pa,Lexical&report_table_where_clausexm
where xm.element_entry_id=pa.element_entry_id(+)LOV query:$column$.delete_element_entryDELETE_ELEMENT_ENTRYDATETRACK_UPDATE_MODELOV query:$column$.business_groupBUSINESS_GROUPELEMENT_NAMELOV query:$column$.employee_nameEMPLOYEE_NAMEELEMENT_NAMELOV query:$column$.business_groupBUSINESS_GROUPEMPLOYEE_NAMEDefault value:$column$.business_groupBUSINESS_GROUPEMPLOYEE_NUMBERDefault value:$column$.employee_nameEMPLOYEE_NAMEEMPLOYEE_NUMBERLOV query:$column$.business_groupBUSINESS_GROUPINPUT10_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT10_ENTRY_VALUELOV query:$column$.input10_value_nameINPUT10_VALUE_NAMEINPUT10_ENTRY_VALUEDefault value:$column$.business_groupBUSINESS_GROUPINPUT10_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT10_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT10_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT10_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT11_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT11_ENTRY_VALUELOV query:$column$.input11_value_nameINPUT11_VALUE_NAMEINPUT11_ENTRY_VALUELOV query:$column$.business_groupBUSINESS_GROUPINPUT11_VALUE_NAMEDefault value:$column$.business_groupBUSINESS_GROUPINPUT11_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT11_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT11_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT12_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT12_ENTRY_VALUELOV query:$column$.input12_value_nameINPUT12_VALUE_NAMEINPUT12_ENTRY_VALUELOV query:$column$.business_groupBUSINESS_GROUPINPUT12_VALUE_NAMEDefault value:$column$.business_groupBUSINESS_GROUPINPUT12_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT12_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT12_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT13_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT13_ENTRY_VALUELOV query:$column$.input13_value_nameINPUT13_VALUE_NAMEINPUT13_ENTRY_VALUEDefault value:$column$.business_groupBUSINESS_GROUPINPUT13_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT13_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT13_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT13_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT14_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT14_ENTRY_VALUELOV query:$column$.input14_value_nameINPUT14_VALUE_NAMEINPUT14_ENTRY_VALUEDefault value:$column$.business_groupBUSINESS_GROUPINPUT14_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT14_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT14_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT14_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT15_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT15_ENTRY_VALUELOV query:$column$.input15_value_nameINPUT15_VALUE_NAMEINPUT15_ENTRY_VALUELOV query:$column$.business_groupBUSINESS_GROUPINPUT15_VALUE_NAMEDefault value:$column$.business_groupBUSINESS_GROUPINPUT15_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT15_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT15_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT1_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT1_ENTRY_VALUELOV query:$column$.input1_value_nameINPUT1_VALUE_NAMEINPUT1_ENTRY_VALUEDefault value:$column$.business_groupBUSINESS_GROUPINPUT1_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT1_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT1_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT1_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT2_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT2_ENTRY_VALUELOV query:$column$.input2_value_nameINPUT2_VALUE_NAMEINPUT2_ENTRY_VALUEDefault value:$column$.business_groupBUSINESS_GROUPINPUT2_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT2_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT2_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT2_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT3_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT3_ENTRY_VALUELOV query:$column$.input3_value_nameINPUT3_VALUE_NAMEINPUT3_ENTRY_VALUEDefault value:$column$.business_groupBUSINESS_GROUPINPUT3_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT3_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT3_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT3_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT4_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT4_ENTRY_VALUELOV query:$column$.input4_value_nameINPUT4_VALUE_NAMEINPUT4_ENTRY_VALUEDefault value:$column$.business_groupBUSINESS_GROUPINPUT4_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT4_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT4_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT4_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT5_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT5_ENTRY_VALUELOV query:$column$.input5_value_nameINPUT5_VALUE_NAMEINPUT5_ENTRY_VALUELOV query:$column$.business_groupBUSINESS_GROUPINPUT5_VALUE_NAMEDefault value:$column$.business_groupBUSINESS_GROUPINPUT5_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT5_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT5_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT6_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT6_ENTRY_VALUELOV query:$column$.input6_value_nameINPUT6_VALUE_NAMEINPUT6_ENTRY_VALUEDefault value:$column$.business_groupBUSINESS_GROUPINPUT6_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT6_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT6_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT6_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT7_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT7_ENTRY_VALUELOV query:$column$.input7_value_nameINPUT7_VALUE_NAMEINPUT7_ENTRY_VALUEDefault value:$column$.business_groupBUSINESS_GROUPINPUT7_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT7_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT7_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT7_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT8_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT8_ENTRY_VALUELOV query:$column$.input8_value_nameINPUT8_VALUE_NAMEINPUT8_ENTRY_VALUELOV query:$column$.business_groupBUSINESS_GROUPINPUT8_VALUE_NAMEDefault value:$column$.business_groupBUSINESS_GROUPINPUT8_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT8_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT8_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT9_ENTRY_VALUELOV query:$column$.element_nameELEMENT_NAMEINPUT9_ENTRY_VALUELOV query:$column$.input9_value_nameINPUT9_VALUE_NAMEINPUT9_ENTRY_VALUEDefault value:$column$.business_groupBUSINESS_GROUPINPUT9_VALUE_NAMELOV query:$column$.business_groupBUSINESS_GROUPINPUT9_VALUE_NAMELOV query:$column$.element_nameELEMENT_NAMEINPUT9_VALUE_NAMEDefault value:$column$.element_nameELEMENT_NAMEINPUT9_VALUE_NAMEValue to id query:$column$.business_groupBUSINESS_GROUPp_assignment_idValue to id query:$column$.business_groupBUSINESS_GROUPp_element_link_idValue to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id1Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id10Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id11Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id12Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id13Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id14Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id15Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id2Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id3Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id4Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id5Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id6Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id7Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id8Value to id query:$column$.business_groupBUSINESS_GROUPp_input_value_id9Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id1Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id10Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id11Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id12Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id13Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id14Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id15Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id2Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id3Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id4Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id5Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id6Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id7Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id8Value to id query:$column$.element_nameELEMENT_NAMEp_input_value_id9Value to id query:$column$.employee_nameEMPLOYEE_NAMEp_assignment_idValue to id query:$column$.input10_value_nameINPUT10_VALUE_NAMEp_input_value_id10Value to id query:$column$.input11_value_nameINPUT11_VALUE_NAMEp_input_value_id11Value to id query:$column$.input12_value_nameINPUT12_VALUE_NAMEp_input_value_id12Value to id query:$column$.input13_value_nameINPUT13_VALUE_NAMEp_input_value_id13Value to id query:$column$.input14_value_nameINPUT14_VALUE_NAMEp_input_value_id14Value to id query:$column$.input15_value_nameINPUT15_VALUE_NAMEp_input_value_id15Value to id query:$column$.input1_value_nameINPUT1_VALUE_NAMEp_input_value_id1Value to id query:$column$.input2_value_nameINPUT2_VALUE_NAMEp_input_value_id2Value to id query:$column$.input3_value_nameINPUT3_VALUE_NAMEp_input_value_id3Value to id query:$column$.input4_value_nameINPUT4_VALUE_NAMEp_input_value_id4Value to id query:$column$.input5_value_nameINPUT5_VALUE_NAMEp_input_value_id5Value to id query:$column$.input6_value_nameINPUT6_VALUE_NAMEp_input_value_id6Value to id query:$column$.input7_value_nameINPUT7_VALUE_NAMEp_input_value_id7Value to id query:$column$.input8_value_nameINPUT8_VALUE_NAMEp_input_value_id8Value to id query:$column$.input9_value_nameINPUT9_VALUE_NAMEp_input_value_id9