Exception for > 80 hours in any consecutive two weeks

--- AE STEP: 5
--- SQL Object ID: KLREX
---

INSERT INTO PS_TL_TA_RESEQX SELECT %PROCESSINSTANCE,A.EMPLID,A.EMPL_RCD,B.START_DT,ROWNUM,0,' ',0 FROM PS_TL_TR_STATUS A,PS_TL_CALENDAR B
WHERE A.EMPLID IN (SELECT EMPLID FROM PS_TL_IPT1 )
AND B.PERIOD_ID = 'PSWEEK' AND B.START_DT BETWEEN A.EARLIEST_CHGDT AND A.PRCS_THRU_DT

--------------------------------------------------------------------------------------------
--- AE STEP: 10
--- SQL Object ID: KLREX1
---

INSERT INTO PS_TL_TA_RESEQX SELECT %PROCESSINSTANCE,A.EMPLID,A.EMPL_RCD,B.START_DT,0,0,' ',0 FROM PS_TL_TR_STATUS A,PS_TL_CALENDAR B
WHERE (A.EMPLID,A.EMPL_RCD) IN (SELECT EMPLID,EMPL_RCD FROM PS_TL_IPT1 )
AND B.PERIOD_ID = 'PSWEEK'
AND B.START_DT = (SELECT MAX(B1.START_DT) FROM PS_TL_CALENDAR B1
                  WHERE B1.PERIOD_TYPE = B.PERIOD_TYPE AND B1.PERIOD_ID = B.PERIOD_ID AND B1.START_DT < A.EARLIEST_CHGDT)

--------------------------------------------------------------------------------------------
--- AE STEP: 15
--- SQL Object ID: KUPDREX
---

UPDATE PS_TL_TA_RESEQX B
SET B.TL_QUANTITY = (SELECT SUM(A.TL_QUANTITY) FROM PS_TL_IPT1 A
                     WHERE A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD
                     AND A.DUR BETWEEN B.DUR AND B.DUR + 13
                     GROUP BY B.PROCESS_INSTANCE ,B.EMPLID,B.EMPL_RCD,B.DUR)
WHERE EXISTS (SELECT 'X' FROM  PS_TL_IPT1 A
                     WHERE A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD
                     AND A.DUR BETWEEN B.DUR AND B.DUR + 13
                     GROUP BY B.PROCESS_INSTANCE ,B.EMPLID,B.EMPL_RCD,B.DUR)

--------------------------------------------------------------------------------------------
--- AE STEP: 20
--- SQL Object ID: KLDACMTL
---

INSERT INTO PS_TL_WRK23B
SELECT A.PROCESS_INSTANCE,A.EMPLID,A.EMPL_RCD,A.DUR,B.SEQ_NBR,0,0,SUM(A.TL_QUANTITY),0,0,0,0  FROM PS_TL_IPT1 A, PS_TL_TA_RESEQX B
WHERE A.PROCESS_INSTANCE = B.PROCESS_INSTANCE
AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD
AND A.DUR BETWEEN B.DUR AND B.DUR + 13
GROUP BY A.PROCESS_INSTANCE,A.EMPLID,A.EMPL_RCD,B.DUR,B.SEQ_NBR,A.DUR
ORDER BY B.SEQ_NBR,A.DUR

--------------------------------------------------------------------------------------------
--- AE STEP: 25
--- SQL Object ID: KLDACMTL1
---

UPDATE PS_TL_WRK23B A SET A.TL_QUANTITY2 = (SELECT SUM(B.TL_QUANTITY) FROM PS_TL_WRK23B B
WHERE A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.SEQ_NBR = B.SEQ_NBR AND B.DUR <= A.DUR
GROUP BY A.PROCESS_INSTANCE,A.EMPLID,A.EMPL_RCD,A.DUR,A.SEQ_NBR)
WHERE EXISTS (SELECT 'X' FROM PS_TL_WRK23B B
WHERE A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.SEQ_NBR = B.SEQ_NBR AND B.DUR <= A.DUR
GROUP BY A.PROCESS_INSTANCE,A.EMPLID,A.EMPL_RCD,A.DUR,A.SEQ_NBR)

-------------------------------------------------------------------------------------------

Step 30:

INSERT INTO PS_TL_EXCEPTION
SELECT A.PROCESS_INSTANCE,A.EMPLID,A.EMPL_RCD,A.DUR, A.DUR+13,A.SEQ_NBR,B.DUR,B.TL_QUANTITY2 FROM PS_TL_TA_RESEQX4 A, PS_TL_WRK23B4 B
WHERE A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.SEQ_NBR = B.SEQ_NBR
AND A.TL_QUANTITY > 80 AND B.TL_QUANTITY2 > 80