3 days no punch

--- AE STEP: 5
---
%Truncatetable (PS_TL_IPT2)
(DELETE FROM PS_TL_IPT2)
--------------------------------------------------------------------------------------------
--- AE STEP: 7
---
%Truncatetable (PS_TL_IPT3)
(DELETE FROM PS_TL_IPT3)
--------------------------------------------------------------------------------------------
--- AE STEP: 10
--- SQL Object ID: FDINDNOPUNCH_01
---
INSERT INTO %Table(TL_IPT2)
SELECT %BIND(PROCESS_INSTANCE), A.EMPLID,A.EMPL_RCD,A.DUR,500,' ','','',0,'','',0,0,0,' ',' ',0,0,0,' ',' ',' ',' ',' ',' ',' ',0,0,0
,0,0,' ',' ',' ',' ',' ',0,0,' ',' ',' ',' ',0,' ',' ',' ',' ',' ',' ',0,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',0,' ',' ',0,0
FROM %Table(WRK_ADHOC_TAO) A
WHERE  A.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND A.OFFDAY_IND = 'N'
AND A.HOLIDAY_FLAG = 'N'
GROUP BY A.PROCESS_INSTANCE,A.EMPLID,A.EMPL_RCD,A.DUR
--------------------------------------------------------------------------------------------
--- AE STEP: 15
--- SQL Object ID: FDINDNOPCH_RANK
---
INSERT INTO %Table(TL_IPT3)
SELECT %BIND(PROCESS_INSTANCE), A.EMPLID,A.EMPL_RCD,A.DUR,500,' ','','',0,'','',0,0,0,' ',' ',0,0,0,' ',' ',' ',' ',' ',' ',' ',0,0,0
,RANK() OVER (ORDER BY A.DUR),0,' ',' ',' ',' ',' ',0,0,' ',' ',' ',' ',0,' ',' ',' ',' ',' ',' ',0,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',0,' ',' ',0,0
FROM %Table(TL_IPT2) A
WHERE  A.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
--------------------------------------------------------------------------------------------
--- AE STEP: 17
--- SQL Object ID: FDINDNP_01
---
UPDATE %Table(TL_IPT3) A SET A.USER_FIELD_1 = (CASE WHEN A.DUR IN (SELECT B.DUR FROM %Table(TL_IPT1) B WHERE B.PROCESS_INSTANCE =  %Bind(PROCESS_INSTANCE) AND B.TRC IN (SELECT VALUEGROUP FROM PS_TL_VAL_LIST_DTL WHERE LIST_ID = ''')) THEN 'Y' ELSE 'N' END) WHERE A.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
--------------------------------------------------------------------------------------------
--- AE STEP: 18
--- SQL Object ID: FDINDNP_02
---
UPDATE %Table(TL_IPT3) A SET A.RULE_FLAG3 = 1
WHERE A.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND A.USER_FIELD_1 = 'N'
AND EXISTS(SELECT 'X' FROM %Table(TL_IPT3) B WHERE B.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND B.RULE_FLAG4 = (A.RULE_FLAG4-1) AND B.USER_FIELD_1 = 'N')
AND EXISTS(SELECT 'X' FROM %Table(TL_IPT3) C WHERE  C.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND C.RULE_FLAG4 = (A.RULE_FLAG4-2) AND C.USER_FIELD_1 = 'N')
--------------------------------------------------------------------------------------------
--- AE STEP: 19
--- SQL Object ID: FDINDNP_03
---
INSERT INTO %Table(TL_EXCEPT_WRK)
(PROCESS_INSTANCE
,VALID_INST_ID
,EXCEPTION_ID          
,EMPLID                
,EMPL_RCD              
,DUR                   
,SEQ_NBR               
,EXCEPTION_SOURCE
,UPDATE_FLAG           
,RUN_CNTL_ID           
,EXCEPTION_STATUS
,RESOLVED_BY           
,ACTION_DTTM           
,SEVERITY              
,START_DT              
,END_DT                
,ALLOW_IND             
,MSG_DATA1             
,MSG_DATA2             
,MSG_DATA3             
,MSG_DATA4             
,MSG_DATA5)
SELECT
%Bind(A.PROCESS_INSTANCE)
,0         
,'FDIN0001'
,A.EMPLID                
,A.EMPL_RCD              
,A.DUR                   
,A.SEQ_NBR               
,'TA'
,'U'           
,' '
,'U'
,' '
,%CurrentDateTimeIn           
,'L'              
,A.DUR
,A.DUR
,'Y'             
,' '
,%Bind(TL_EXC_RULE_AET.DESCR)
,' '
,' '
,' '
FROM %Table(TL_IPT3) A
WHERE A.PROCESS_INSTANCE = %bind(PROCESS_INSTANCE)
AND NOT EXISTS (SELECT 'X' FROM %Table(TL_EXCEPT_WRK) D
WHERE A.PROCESS_INSTANCE = D.PROCESS_INSTANCE
AND A.EMPLID = D.EMPLID
AND A.EMPL_RCD = D.EMPL_RCD
AND A.DUR = D.DUR
AND D.EXCEPTION_ID = 'FDIN0001')
AND A.RULE_FLAG3 = 1
AND A.USER_FIELD_1 = 'N'
AND A.DUR <= SYSDATE