SELECT peo.person_number emp_id, aapft.NAME absence_plan_name,
acc_ent.end_bal balance
FROM per_all_people_f peo,
per_all_assignments_f asg,
per_person_names_f per_name,
per_periods_of_service ser,
per_person_types_tl per_typ,
per_legal_employers ple,
anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf,
anc_per_accrual_entries acc_ent
WHERE asg.person_id = peo.person_id
AND ser.person_id = peo.person_id
AND per_name.person_id = peo.person_id
AND acc_ent.person_id = peo.person_id
AND asg.period_of_service_id = ser.period_of_service_id
AND acc_ent.prd_of_svc_id = asg.period_of_service_id
AND asg.person_type_id = per_typ.person_type_id
AND asg.legal_entity_id = ple.organization_id
AND aapft.absence_plan_id = aapf.absence_plan_id
AND acc_ent.plan_id = aapf.absence_plan_id
AND acc_ent.accrual_period =
(SELECT MAX (acc_ent1.accrual_period)
FROM anc_per_accrual_entries acc_ent1
WHERE acc_ent1.accrual_period <=
TO_DATE (SUBSTR (TRUNC (SYSDATE), 1, 10))
AND acc_ent1.person_id = acc_ent.person_id
AND acc_ent1.prd_of_svc_id = acc_ent.prd_of_svc_id
AND acc_ent1.plan_id = acc_ent.plan_id)
AND asg.primary_flag = 'Y'
AND asg.assignment_type IN ('E', 'C', 'N', 'P')
AND asg.assignment_status_type = 'ACTIVE'
AND per_name.name_type = 'GLOBAL'
AND ple.status = 'A'
AND per_typ.LANGUAGE = USERENV ('LANG')
AND aapf.plan_status = 'A'
AND aapft.LANGUAGE = 'US'
--AND acc_ent.end_bal <> 0
AND TRUNC (SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date
AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND TRUNC (SYSDATE) BETWEEN per_name.effective_start_date
AND per_name.effective_end_date
AND TRUNC (SYSDATE) BETWEEN ple.effective_start_date AND ple.effective_end_date
AND TRUNC (SYSDATE) BETWEEN aapf.effective_start_date
AND aapf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN aapft.effective_start_date
AND aapft.effective_end_date
acc_ent.end_bal balance
FROM per_all_people_f peo,
per_all_assignments_f asg,
per_person_names_f per_name,
per_periods_of_service ser,
per_person_types_tl per_typ,
per_legal_employers ple,
anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf,
anc_per_accrual_entries acc_ent
WHERE asg.person_id = peo.person_id
AND ser.person_id = peo.person_id
AND per_name.person_id = peo.person_id
AND acc_ent.person_id = peo.person_id
AND asg.period_of_service_id = ser.period_of_service_id
AND acc_ent.prd_of_svc_id = asg.period_of_service_id
AND asg.person_type_id = per_typ.person_type_id
AND asg.legal_entity_id = ple.organization_id
AND aapft.absence_plan_id = aapf.absence_plan_id
AND acc_ent.plan_id = aapf.absence_plan_id
AND acc_ent.accrual_period =
(SELECT MAX (acc_ent1.accrual_period)
FROM anc_per_accrual_entries acc_ent1
WHERE acc_ent1.accrual_period <=
TO_DATE (SUBSTR (TRUNC (SYSDATE), 1, 10))
AND acc_ent1.person_id = acc_ent.person_id
AND acc_ent1.prd_of_svc_id = acc_ent.prd_of_svc_id
AND acc_ent1.plan_id = acc_ent.plan_id)
AND asg.primary_flag = 'Y'
AND asg.assignment_type IN ('E', 'C', 'N', 'P')
AND asg.assignment_status_type = 'ACTIVE'
AND per_name.name_type = 'GLOBAL'
AND ple.status = 'A'
AND per_typ.LANGUAGE = USERENV ('LANG')
AND aapf.plan_status = 'A'
AND aapft.LANGUAGE = 'US'
--AND acc_ent.end_bal <> 0
AND TRUNC (SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date
AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND TRUNC (SYSDATE) BETWEEN per_name.effective_start_date
AND per_name.effective_end_date
AND TRUNC (SYSDATE) BETWEEN ple.effective_start_date AND ple.effective_end_date
AND TRUNC (SYSDATE) BETWEEN aapf.effective_start_date
AND aapf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN aapft.effective_start_date
AND aapft.effective_end_date
Hi Prathap,
ReplyDeleteIts really very helpful. Actually,I need this accrual balance on a particular date for a particular employee. That means - parameter will be Date and Person number.
Could you please send me that.
Thanks in advance.
Hello Krish, Did you get the report with date parameter as requirement ?
DeleteDoes anyone have query for this balance as per date requirement?
Delete