VIEW: APPS.PER_ZA_TIME_PERIODS_V3

Source

View Text - Preformatted

SELECT p2.payroll_id, p2.start_date, p2.end_date, p1.prd_information3 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_information3 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, to_char(to_number(p1.prd_information3) - 1) from per_time_periods p1 where p1.start_date = ( select min(p2.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_information3 from per_time_periods p1 where p1.start_date = ( select min(p2.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, to_char(to_number(p1.prd_information3) + 1) from per_time_periods p1 where p1.start_date = ( select max(p2.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_information3 from per_time_periods p1 where p1.start_date = ( select max(p2.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_INFORMATION3
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_INFORMATION3
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
, TO_CHAR(TO_NUMBER(P1.PRD_INFORMATION3) - 1)
FROM PER_TIME_PERIODS P1
WHERE P1.START_DATE = ( SELECT MIN(P2.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_INFORMATION3
FROM PER_TIME_PERIODS P1
WHERE P1.START_DATE = ( SELECT MIN(P2.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
, TO_CHAR(TO_NUMBER(P1.PRD_INFORMATION3) + 1)
FROM PER_TIME_PERIODS P1
WHERE P1.START_DATE = ( SELECT MAX(P2.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_INFORMATION3
FROM PER_TIME_PERIODS P1
WHERE P1.START_DATE = ( SELECT MAX(P2.START_DATE)
FROM PER_TIME_PERIODS P2
WHERE P2.PAYROLL_ID = P1.PAYROLL_ID )