VIEW: APPS.PER_ZA_TIME_PERIODS_V2

Source

View Text - Preformatted

SELECT p2.payroll_id, p2.start_date, p2.end_date, p1.prd_information2 from per_time_periods p1, per_time_periods p2 WHERE p1.payroll_id = p2.payroll_id and p1.end_date = p2.start_date - 1 union select p1.payroll_id, p1.start_date, p1.end_date, p2.prd_information2 from per_time_periods p1, per_time_periods p2 where p1.payroll_id = p2.payroll_id and p2.start_date = p1.end_date + 1 union select p1.payroll_id, p1.start_date, p1.end_date, decode(p1.prd_information2, '1', '4', '2', '1', '3', '2', '4', '3') from per_time_periods p1 where p1.start_date = ( select min(start_date) from per_time_periods p2 where p2.payroll_id = p1.payroll_id ) union select p1.payroll_id, p1.start_date, p1.end_date, p1.prd_information2 from per_time_periods p1 where p1.start_date = ( select min(start_date) from per_time_periods p2 where p2.payroll_id = p1.payroll_id ) union select p1.payroll_id, p1.start_date, p1.end_date, decode(p1.prd_information2, '1', '2', '2', '3', '3', '4', '4', '1') from per_time_periods p1 where p1.start_date = ( select max(start_date) from per_time_periods p2 where p2.payroll_id = p1.payroll_id ) union select p1.payroll_id, p1.start_date, p1.end_date, p1.prd_information2 from per_time_periods p1 where p1.start_date = ( select max(start_date) from per_time_periods p2 where p2.payroll_id = p1.payroll_id )

View Text - HTML Formatted

SELECT P2.PAYROLL_ID
, P2.START_DATE
, P2.END_DATE
, P1.PRD_INFORMATION2
FROM PER_TIME_PERIODS P1
, PER_TIME_PERIODS P2
WHERE P1.PAYROLL_ID = P2.PAYROLL_ID
AND P1.END_DATE = P2.START_DATE - 1 UNION SELECT P1.PAYROLL_ID
, P1.START_DATE
, P1.END_DATE
, P2.PRD_INFORMATION2
FROM PER_TIME_PERIODS P1
, PER_TIME_PERIODS P2
WHERE P1.PAYROLL_ID = P2.PAYROLL_ID
AND P2.START_DATE = P1.END_DATE + 1 UNION SELECT P1.PAYROLL_ID
, P1.START_DATE
, P1.END_DATE
, DECODE(P1.PRD_INFORMATION2
, '1'
, '4'
, '2'
, '1'
, '3'
, '2'
, '4'
, '3')
FROM PER_TIME_PERIODS P1
WHERE P1.START_DATE = ( SELECT MIN(START_DATE)
FROM PER_TIME_PERIODS P2
WHERE P2.PAYROLL_ID = P1.PAYROLL_ID ) UNION SELECT P1.PAYROLL_ID
, P1.START_DATE
, P1.END_DATE
, P1.PRD_INFORMATION2
FROM PER_TIME_PERIODS P1
WHERE P1.START_DATE = ( SELECT MIN(START_DATE)
FROM PER_TIME_PERIODS P2
WHERE P2.PAYROLL_ID = P1.PAYROLL_ID ) UNION SELECT P1.PAYROLL_ID
, P1.START_DATE
, P1.END_DATE
, DECODE(P1.PRD_INFORMATION2
, '1'
, '2'
, '2'
, '3'
, '3'
, '4'
, '4'
, '1')
FROM PER_TIME_PERIODS P1
WHERE P1.START_DATE = ( SELECT MAX(START_DATE)
FROM PER_TIME_PERIODS P2
WHERE P2.PAYROLL_ID = P1.PAYROLL_ID ) UNION SELECT P1.PAYROLL_ID
, P1.START_DATE
, P1.END_DATE
, P1.PRD_INFORMATION2
FROM PER_TIME_PERIODS P1
WHERE P1.START_DATE = ( SELECT MAX(START_DATE)
FROM PER_TIME_PERIODS P2
WHERE P2.PAYROLL_ID = P1.PAYROLL_ID )