*****************Assigned Payroll***********************
select distinct
peo.person_number,
ppr.payroll_relationship_number,
asg.assignment_number,
ppp.payroll_name,
to_char(asg.RELATIONSHIP_GROUP_ID) as payroll_assignment_id,
to_char(pap.assigned_payroll_id) as assigned_payroll_id,
to_char(pap.start_date, 'YYYY/MM/DD') as ppr_start,
to_char(pap.end_date, 'YYYY/MM/DD') as ppr_end,
popm.ORG_PAYMENT_METHOD_NAME,
min(ppp.effective_start_date)||'~'||max(ppp.effective_end_date) payroll_range,
min(pou.effective_start_date)||'~'||max(pou.effective_end_date) usage_range,
min(popm.effective_start_date)||'~'||max(popm.effective_end_date) opm_range
from pay_pay_relationships_dn ppr, /* payroll relationship */
pay_dates rdt,
pay_time_definitions rtd,
per_all_people_f peo,
pay_rel_groups_dn asg,
PAY_ASSIGNED_PAYROLLS_DN pap,
PAY_ALL_PAYROLLS_F ppp,
PAY_ORG_PAY_METHOD_USAGES_F pou,
PAY_ORG_PAY_METHODS_vl popm
where peo.person_number = 'WK_1997'
and ppr.person_id = peo.person_id
and asg.PAYROLL_RELATIONSHIP_ID= ppr.PAYROLL_RELATIONSHIP_ID
and asg.GROUP_TYPE='A'
and asg.PARENT_REL_GROUP_ID = pap.PAYROLL_TERM_ID
and pap.payroll_id = ppp.payroll_id
and pou.payroll_id (+) = pap.payroll_id
and popm.ORG_PAYMENT_METHOD_ID (+) = pou.ORG_PAYMENT_METHOD_ID
--and pap.start_date between ppp.effective_start_date and ppp.effective_end_date
--and pap.start_date between pou.effective_start_date (+) and pou.effective_end_date (+)
--and pou.effective_start_date between popm.effective_start_date (+) and popm.effective_end_date (+)
and rdt.source_id (+) = asg.RELATIONSHIP_GROUP_ID
and rdt.source_type (+) = 'PA'
and rtd.time_definition_id (+) = rdt.time_definition_id
group by peo.person_number,
ppr.payroll_relationship_number,
asg.assignment_number,
ppp.payroll_name,
to_char(asg.RELATIONSHIP_GROUP_ID),
to_char(pap.assigned_payroll_id),
to_char(pap.start_date, 'YYYY/MM/DD'),
to_char(pap.end_date, 'YYYY/MM/DD'),
popm.ORG_PAYMENT_METHOD_NAME
order by peo.person_number,
ppr.payroll_relationship_number,
asg.assignment_number,
ppp.payroll_name,
to_char(asg.RELATIONSHIP_GROUP_ID),
to_char(pap.assigned_payroll_id),
to_char(pap.start_date, 'YYYY/MM/DD'),
to_char(pap.end_date, 'YYYY/MM/DD'),
popm.ORG_PAYMENT_METHOD_NAME
*****************Element setup***********************
select petf.element_type_id
,pettl.element_name
,petf.processing_type Recurring_NonRecurring
,petf.effective_start_date
,petf.effective_end_date
,petf.multiple_entries_allowed_flag
,pivf.base_name
from pay_element_types_f petf
,pay_element_types_tl pettl
,pay_input_values_f pivf
where petf.element_type_id = pettl.element_type_id
and pivf.element_type_id=petf.element_type_id
and pettl.element_name = 'WK_Bonus'
and pettl.language='US'
and pivf.user_enterable_flag='Y'
*****************Element Entries***********************
select aaa.person_number,aaa.element_name,aaa.creator_type,aaa.created_by,aaa.creation_Date,aaa.entry_range,
(select min(date_from)||'~'||max(date_to) from pay_entry_usages where element_entry_id=aaa.element_entry_id) usage_range,
aaa.element_entry_id,
(select --LISTAGG(piv.name, '; ') WITHIN GROUP (ORDER BY piv.display_sequence)
rtrim(xmlserialize(content extract(xmlagg(xmlelement("e", piv.name||'; ') order by piv.display_sequence), '//text()')), ',' )
from PAY_INPUT_VALUES_VL piv
where piv.element_type_id = aaa.element_type_id and aaa.start_Date between piv.effective_start_date and piv.EFFECTIVE_END_DATE) inputs,
(select --LISTAGG(piv.name||'='||peev.screen_entry_value, '; ') WITHIN GROUP (ORDER BY piv.display_sequence)
rtrim(xmlserialize(content extract(xmlagg(xmlelement("e", piv.name||'='||peev.screen_entry_value||'; ') order by piv.display_sequence), '//text()')), ',' )
from PAY_INPUT_VALUES_VL piv,pay_element_entry_values_f peev
where peev.element_entry_id = aaa.element_entry_id and peev.input_value_id = piv.input_value_id
and piv.element_type_id = aaa.element_type_id and aaa.start_Date between piv.effective_start_date and piv.EFFECTIVE_END_DATE
and aaa.start_Date between peev.effective_start_date and peev.EFFECTIVE_END_DATE) input_values,
(select max('PERL:'||prl.payroll_relationship_number||'('||prl.payroll_relationship_id||'); '||'TERM:'||trm.assignment_number||'('||trm.relationship_group_id||'); '||'ASG:'||asg.assignment_number||'('||asg.relationship_group_id||'); ')
from pay_entry_usages peu,
pay_rel_groups_dn asg,
pay_rel_groups_dn trm,
PAY_PAY_RELATIONSHIPS_DN prl
where peu.element_entry_id=aaa.element_entry_id
and peu.payroll_assignment_id=asg.relationship_group_id (+)
and peu.payroll_term_id=trm.relationship_group_id (+)
and peu.payroll_relationship_id = prl.payroll_relationship_id
) Payroll_Employment,
(select max('TERM:'||htrm.assignment_number||'('||htrm.assignment_id||'); '||'ASG:'||hasg.assignment_number||'('||hasg.assignment_id||'); ')
from pay_entry_usages peu,
pay_rel_groups_dn asg,
pay_rel_groups_dn trm,
per_all_assignments_m hasg,
per_all_assignments_m htrm
where peu.element_entry_id=aaa.element_entry_id
and peu.payroll_assignment_id=asg.relationship_group_id (+)
and peu.payroll_assignment_id=trm.relationship_group_id (+)
and hasg.assignment_id (+) = asg.assignment_id
and htrm.assignment_id (+) = trm.term_id
) HR_Employment
from (select peo.person_number,
pet.element_name,
pee.creator_type,
pee.created_by,
pee.creation_Date,
pee.element_entry_id,
pee.element_type_id,
min(pee.effective_start_date) as start_Date,
min(pee.effective_start_date)||'~'||max(pee.effective_end_date) entry_range
from per_all_people_f peo,
pay_element_entries_f pee,
pay_element_types_vl pet
where peo.person_number = 'WK_1997'
and pee.person_id = peo.person_id
and pee.element_type_id = pet.element_type_id
group by peo.person_number,
pet.element_name,
pee.element_type_id,
pee.creator_type,
pee.created_by,
pee.creation_Date,
pee.element_entry_id) aaa
order by aaa.person_number,
aaa.element_name,
aaa.creator_type,
aaa.created_by,
aaa.creation_Date,
aaa.element_entry_id
No comments:
Post a Comment