0
0
WITH SplitPersonNumbers AS (
SELECT
REGEXP_SUBSTR(:person_numbers, '[^,]+', 1, LEVEL) AS person_number,
LEVEL AS sort_order
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(:person_numbers, ',') + 1
),
RankedAssignments AS (
SELECT
papf.person_number AS PersonNumber,
paam.assignment_id AS AssignmentId,
paam.work_terms_assignment_id AS WorkTermsAssignmentId,
ppos.period_of_service_id AS PeriodOfServiceId,
ROW_NUMBER() OVER (PARTITION BY papf.person_number ORDER BY paam.effective_start_date DESC) AS rn,
spn.sort_order
FROM
per_all_people_f papf
JOIN per_person_names_f ppnf ON ppnf.person_id = papf.person_id
JOIN per_periods_of_service ppos ON ppos.person_id = papf.person_id
JOIN per_all_assignments_m paam ON paam.period_of_service_id = ppos.period_of_service_id
JOIN per_all_assignments_m workterm ON workterm.assignment_id = paam.work_terms_assignment_id
JOIN SplitPersonNumbers spn ON spn.person_number = papf.person_number
WHERE
SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND ppnf.name_type = 'GLOBAL'
AND SYSDATE BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND paam.assignment_status_type IN ('ACTIVE', 'Active - Payroll Eligible')
AND SYSDATE BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.primary_assignment_flag = 'Y'
AND SYSDATE BETWEEN workterm.effective_start_date AND workterm.effective_end_date
),
FinalResult AS (
SELECT
ROW_NUMBER() OVER (ORDER BY sort_order) AS seq,
PersonNumber,
AssignmentId,
WorkTermsAssignmentId,
PeriodOfServiceId,
sort_order
FROM RankedAssignments
WHERE rn = 1
)
SELECT
LPAD(seq, 2, '0') AS SequenceNumber,
PersonNumber,
AssignmentId,
WorkTermsAssignmentId,
PeriodOfServiceId
FROM FinalResult
ORDER BY sort_order
I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.