--- AE STEP: 2 ---
SQL Object ID:
KREPH29 --- UPDATE PS_TL_IPT1 A SET A.SEQ_NBR = A.SEQ_NBR + 200,
A.TL_RULE_ID = 'KREPH56', A.TRC = 'KUEPH' WHERE A.PROCESS_INSTANCE =
%BIND(PROCESS_INSTANCE) AND A.DUR <> TRUNC(A.PUNCH_BEGIN) AND
TRUNC(A.PUNCH_BEGIN) = TRUNC(A.PUNCH_END) AND A.PUNCH_TYPE = '1' AND
A.RT_SOURCE NOT IN ('AM','SCH') AND EXISTS (SELECT 'X' FROM PS_WRK_ADHOC_TAO B
WHERE A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND
A.EMPL_RCD = B.EMPL_RCD AND A.DUR = B.DUR AND B.HOLIDAY_FLAG = 'Y')
--------------------------------------------------------------------------------------------
---
AE STEP: 3 ---
SQL Object ID: KREPH30 --- UPDATE PS_TL_IPT1 A SET A.DUR = A.DUR + 1,
A.SEQ_NBR = A.SEQ_NBR + 300, A.TL_RULE_ID = 'KREPH56', A.TRC = 'GPREX' WHERE
A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND A.DUR <>
TRUNC(A.PUNCH_BEGIN) AND TRUNC(A.PUNCH_BEGIN) = TRUNC(A.PUNCH_END) AND
A.PUNCH_TYPE = '1' AND A.RT_SOURCE NOT IN ('AM','SCH') AND EXISTS (SELECT 'X'
FROM PS_WRK_ADHOC_TAO B WHERE A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND
A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND TRUNC(A.PUNCH_BEGIN) =
B.DUR AND B.HOLIDAY_FLAG = 'Y')
--------------------------------------------------------------------------------------------
---
AE STEP: 5 ---
SQL Object ID: KREPH22 --- %INSERTSELECT(TL_IPT1,TL_IPT1 A, PUNCH_TYPE =
'1', PUNCH_BEGIN = %DateTimein('1900-01-01-00:00:00.000000'), PUNCH_BEGIN_R =
%DateTimein('1900-01-01-00:00:00.000000'), TIME_IN_MIN1 = 0, PUNCH_END =
%DateTimein('1900-01-01-00:00:00.000000'), PUNCH_END_R = %DateTimein('1900-01-01-00:00:00.000000'),
TIME_IN_MIN2 = 0, DUR = A.DUR + 1, SEQ_NBR = '100', TL_QUANTITY =
A.TIME_IN_MIN2/60,TL_RULE_ID = 'KREPH56',TRC = 'AUSPH') FROM PS_TL_IPT1 A WHERE
A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND A.DUR = TRUNC(A.PUNCH_BEGIN)
AND A.DUR <> TRUNC(A.PUNCH_END) AND A.PUNCH_TYPE = '1' AND A.RT_SOURCE
NOT IN ('AM','SCH') AND EXISTS (SELECT 'X' FROM PS_WRK_ADHOC_TAO B WHERE
A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD
= B.EMPL_RCD AND TRUNC(A.PUNCH_END) = B.DUR AND B.HOLIDAY_FLAG = 'Y')
--------------------------------------------------------------------------------------------
---
AE STEP: 6 ---
SQL Object ID:
KREPH23 ---
%INSERTSELECT(TL_IPT1,TL_IPT1 A, PUNCH_TYPE = '1', PUNCH_BEGIN = %DateTimein('1900-01-01-00:00:00.000000'),
PUNCH_BEGIN_R = %DateTimein('1900-01-01-00:00:00.000000'), TIME_IN_MIN1 = 0,
PUNCH_END = %DateTimein('1900-01-01-00:00:00.000000'), PUNCH_END_R =
%DateTimein('1900-01-01-00:00:00.000000'), TIME_IN_MIN2 = 0, SEQ_NBR = '105',
TL_QUANTITY = A.TIME_IN_MIN2/60,TL_RULE_ID = 'KREPH56',TRC = 'KUEPH') FROM
PS_TL_IPT1 A WHERE A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND A.DUR =
TRUNC(A.PUNCH_BEGIN) AND A.DUR <> TRUNC(A.PUNCH_END) AND A.PUNCH_TYPE =
'1' AND A.RT_SOURCE NOT IN ('AM','SCH') AND EXISTS (SELECT 'X' FROM
PS_WRK_ADHOC_TAO B WHERE A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID =
B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.DUR = B.DUR AND B.HOLIDAY_FLAG =
'Y') AND EXISTS (SELECT 'X' FROM PS_WRK_ADHOC_TAO B WHERE A.PROCESS_INSTANCE =
B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND
TRUNC(A.PUNCH_END) = B.DUR AND B.HOLIDAY_FLAG = 'N')
--------------------------------------------------------------------------------------------
---
AE STEP: 9 ---
SQL Object ID: KREPH24 --- UPDATE PS_TL_IPT1 A SET A.TL_QUANTITY = (1440 -
TIME_IN_MIN1)/60, A.TL_RULE_ID = 'KREPH56',A.TRC = 'GPREX' WHERE
A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND A.DUR = TRUNC(A.PUNCH_BEGIN)
AND A.DUR <> TRUNC(A.PUNCH_END) AND A.PUNCH_TYPE = '1' AND A.RT_SOURCE
NOT IN ('AM','SCH') AND EXISTS (SELECT 'X' FROM PS_WRK_ADHOC_TAO B WHERE
A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD
= B.EMPL_RCD AND TRUNC(A.PUNCH_END) = B.DUR AND B.HOLIDAY_FLAG = 'Y') AND
EXISTS (SELECT 'X' FROM PS_WRK_ADHOC_TAO B WHERE A.PROCESS_INSTANCE =
B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND
A.DUR = B.DUR AND B.HOLIDAY_FLAG = 'N')
--------------------------------------------------------------------------------------------
---
AE STEP: 10 ---
SQL Object ID: KREPH25 --- UPDATE PS_TL_IPT1 A SET A.TL_QUANTITY = (1440 -
TIME_IN_MIN1)/60, A.TL_RULE_ID = 'KREPH56',A.TRC = 'AUSPH' WHERE
A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND A.DUR = TRUNC(A.PUNCH_BEGIN)
AND A.DUR <> TRUNC(A.PUNCH_END) AND A.PUNCH_TYPE = '1' AND A.RT_SOURCE
NOT IN ('AM','SCH') AND EXISTS (SELECT 'X' FROM PS_WRK_ADHOC_TAO B WHERE
A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD
= B.EMPL_RCD AND A.DUR = B.DUR AND B.HOLIDAY_FLAG = 'Y')
--------------------------------------------------------------------------------------------
---
AE STEP: 12 --- %Truncatetable (PS_TL_IPT2) (DELETE FROM PS_TL_IPT2)
--------------------------------------------------------------------------------------------
---
AE STEP: 15 --- %Truncatetable (PS_FDC_SCHED_TAO) (DELETE FROM
PS_FDC_SCHED_TAO)
--------------------------------------------------------------------------------------------
---
AE STEP: 18 ---
SQL Object ID: FDSRINSIPT2 --- %INSERTSELECT(TL_IPT2,TL_IPT1 A) FROM
PS_TL_IPT1 A WHERE A.PROCESS_INSTANCE =%BIND(PROCESS_INSTANCE) AND DUR BETWEEN
%BIND(FROMDATE) AND %BIND(THRUDATE)
--------------------------------------------------------------------------------------------
---
AE STEP: 21 ---
SQL Object ID: FDSRTRNRES --- %TRUNCATETABLE(PS_TL_TA_RESEQX)
--------------------------------------------------------------------------------------------
---
AE STEP: 24 ---
SQL Object ID: KREPH26 --- INSERT INTO PS_TL_TA_RESEQX SELECT
%BIND(PROCESS_INSTANCE), A.EMPLID,A.EMPL_RCD,A.DUR,1,1,'SUM',SUM(A.TL_QUANTITY)
FROM PS_TL_PAYABLE_TIME A WHERE A.TRC NOT IN ('AUSDC','AUWKD','GPUPD','KUEPH')
AND EXISTS (SELECT 'X' FROM PS_CURRENT_JOB C,PS_HOLIDAY_DATE D WHERE C.EMPLID =
A.EMPLID AND C.HOLIDAY_SCHEDULE = D.HOLIDAY_SCHEDULE AND D.HOLIDAY = A.DUR) AND
A.EMPLID IN (SELECT EMPLID FROM PS_TL_IPT1 ) GROUP BY A.EMPLID,A.EMPL_RCD,A.DUR
--------------------------------------------------------------------------------------------
---
AE STEP: 27 ---
SQL Object ID: KREPH27 --- UPDATE PS_TL_TA_RESEQX E SET E.TL_QUANTITY =
(SELECT B.TOT FROM (SELECT %BIND(PROCESS_INSTANCE),
A.EMPLID,A.EMPL_RCD,A.DUR,1,1,'SUM',SUM(A.TL_QUANTITY) TOT FROM PS_TL_IPT1 A
WHERE A.TRC NOT IN ('AUSDC','AUWKD','GPUPD','KUEPH') AND A.PUNCH_TYPE = '1' AND
EXISTS (SELECT 'X' FROM PS_CURRENT_JOB C,PS_HOLIDAY_DATE D WHERE C.EMPLID =
A.EMPLID AND C.HOLIDAY_SCHEDULE = D.HOLIDAY_SCHEDULE AND D.HOLIDAY = A.DUR)
GROUP BY A.EMPLID,A.EMPL_RCD,A.DUR) B WHERE B.EMPLID = E.EMPLID AND B.EMPL_RCD
= E.EMPL_RCD AND B.DUR = E.DUR) WHERE EXISTS (SELECT B.TOT FROM (SELECT
%BIND(PROCESS_INSTANCE), A.EMPLID,A.EMPL_RCD,A.DUR,1,1,'SUM',SUM(A.TL_QUANTITY)
TOT FROM PS_TL_IPT1 A WHERE A.TRC NOT IN ('AUSDC','AUWKD','GPUPD','KUEPH') AND
A.PUNCH_TYPE = '1' AND EXISTS (SELECT 'X' FROM PS_CURRENT_JOB C,PS_HOLIDAY_DATE
D WHERE C.EMPLID = A.EMPLID AND C.HOLIDAY_SCHEDULE = D.HOLIDAY_SCHEDULE AND
D.HOLIDAY = A.DUR) GROUP BY A.EMPLID,A.EMPL_RCD,A.DUR) B WHERE B.EMPLID =
E.EMPLID AND B.EMPL_RCD = E.EMPL_RCD AND B.DUR = E.DUR)
--------------------------------------------------------------------------------------------
---
AE STEP: 30 ---
SQL Object ID: KREPH28 --- INSERT INTO PS_TL_TA_RESEQX E SELECT
%BIND(PROCESS_INSTANCE), B.EMPLID,B.EMPL_RCD,B.DUR,1,1,'SUM',B.TOT FROM (SELECT
A.EMPLID,A.EMPL_RCD,A.DUR,1,1,'SUM',SUM(A.TL_QUANTITY) TOT FROM PS_TL_IPT1 A
WHERE A.TRC NOT IN ('AUSDC','AUWKD','GPUPD','KUEPH') AND A.PUNCH_TYPE = '1' AND
EXISTS (SELECT 'X' FROM PS_CURRENT_JOB C,PS_HOLIDAY_DATE D WHERE C.EMPLID =
A.EMPLID AND C.HOLIDAY_SCHEDULE = D.HOLIDAY_SCHEDULE AND D.HOLIDAY = A.DUR)
GROUP BY A.EMPLID,A.EMPL_RCD,A.DUR) B WHERE NOT EXISTS (SELECT 'X' FROM
PS_TL_TA_RESEQX E WHERE E.EMPLID = B.EMPLID AND E.EMPL_RCD = B.EMPL_RCD AND
E.DUR = B.DUR) --------------------------------------------------------------------------------------------
---
AE STEP: 33 ---
SQL Object ID: FDAUSSUMTLQTY --- INSERT INTO PS_FDC_SCHED_TAO SELECT
B.PROCESS_INSTANCE ,B.EMPLID ,B.EMPL_RCD ,B.DUR ,B.SEQ_NBR1 ,' ' ,0 ,'' ,'' ,'
' ,' ' ,' ' ,' ' ,' ' ,0 ,' ' ,' ' ,'' ,'' ,SUM(A.TL_QUANTITY) ,0 ,0 FROM
PS_TL_TA_RESEQX A ,(SELECT DISTINCT
PROCESS_INSTANCE,EMPLID,EMPL_RCD,DUR,SEQ_NBR1 FROM PS_TL_TA_RESEQX WHERE
PROCESS_INSTANCE = %bind(process_instance) ) B WHERE A.EMPLID = B.EMPLID AND
A.EMPL_RCD = B.EMPL_RCD AND A.SEQ_NBR1 = B.SEQ_NBR1 AND A.DUR <= B.DUR AND
EXTRACT(YEAR FROM A.DUR) = EXTRACT(YEAR FROM B.DUR) GROUP BY
B.PROCESS_INSTANCE,B.SEQ_NBR1,B.EMPLID,B.EMPL_RCD,B.DUR ORDER BY B.DUR
--------------------------------------------------------------------------------------------
---
AE STEP: 36 ---
SQL Object ID: FDAUTRCUPD1 --- UPDATE PS_TL_IPT1 A SET A.TRC = (SELECT
DISTINCT DECODE(OFFDAY_IND,'Y','GPRSC','GPREX') FROM PS_WRK_ADHOC_TAO WHERE
PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND EMPLID = A.EMPLID AND EMPL_RCD =
A.EMPL_RCD AND DUR = A.DUR ) WHERE A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)
AND A.TRC in ( 'AUSPH','GPREX') AND EXISTS(SELECT C.THE_DATE FROM
PS_TL_DATES_TBL C WHERE C.THE_DATE = A.DUR) AND NOT EXISTS (SELECT 'X' FROM
PS_TL_TA_RESEQX B WHERE B.PROCESS_INSTANCE = A.PROCESS_INSTANCE AND B.EMPLID =
A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.DUR < A.DUR) AND NOT EXISTS
(SELECT 'X' FROM PS_TL_EXCEPTION C WHERE C.PROCESS_INSTANCE =
A.PROCESS_INSTANCE AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND
C.DUR < A.DUR) --------------------------------------------------------------------------------------------
---
AE STEP: 39 ---
SQL Object ID: KREPH4 --- UPDATE PS_TL_IPT1 A SET A.TRC = (SELECT DISTINCT
DECODE(OFFDAY_IND,'Y','GPRSC','GPREX') FROM PS_WRK_ADHOC_TAO WHERE
PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND EMPLID = A.EMPLID AND EMPL_RCD =
A.EMPL_RCD AND DUR = A.DUR ) WHERE A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)
AND A.TRC IN ('AUSPH','GPREX') AND EXISTS(SELECT C.THE_DATE FROM
PS_TL_DATES_TBL C WHERE C.THE_DATE = A.DUR) AND EXISTS (SELECT 'X' FROM
PS_FDC_SCHED_TAO B WHERE B.PROCESS_INSTANCE = A.PROCESS_INSTANCE AND B.EMPLID =
A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.DUR = A.DUR AND B.TL_QUANTITY <
56) AND EXISTS (SELECT 'X' FROM PS_WRK_ADHOC_TAO C WHERE C.PROCESS_INSTANCE =
A.PROCESS_INSTANCE AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND
C.DUR = A.DUR AND C.HOLIDAY_FLAG = 'Y')
--------------------------------------------------------------------------------------------
---
AE STEP: 42 ---
SQL Object ID: KREPH12 --- UPDATE PS_TL_TA_RESEQX C SET C.TRC = 'KPHOL'
WHERE EXISTS (SELECT 'X' FROM PS_FDC_SCHED_TAO A, PS_FDC_SCHED_TAO B WHERE
A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND A.PROCESS_INSTANCE =
C.PROCESS_INSTANCE AND A.EMPLID = C.EMPLID AND A.EMPL_RCD = C.EMPL_RCD AND
A.DUR = C.DUR AND A.TL_QUANTITY > 56 AND A.PROCESS_INSTANCE =
B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND
B.DUR = (SELECT MAX(B1.DUR) FROM PS_FDC_SCHED_TAO B1 WHERE B1.PROCESS_INSTANCE
= B.PROCESS_INSTANCE AND B1.EMPLID = B.EMPLID AND B1.EMPL_RCD = B.EMPL_RCD AND
B1.DUR < A.DUR) AND B.TL_QUANTITY >= 56)
--------------------------------------------------------------------------------------------
---
AE STEP: 45 ---
SQL Object ID: KREPH13 --- INSERT INTO PS_TL_TA_RESEQX SELECT
A.PROCESS_INSTANCE,A.EMPLID,A.EMPL_RCD,A.DUR,'2','1','KPHOL',A.TL_QUANTITY - 56
FROM PS_FDC_SCHED_TAO A, PS_FDC_SCHED_TAO B WHERE A.TL_QUANTITY > 56 AND
A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD
= B.EMPL_RCD AND B.DUR = (SELECT MAX(B1.DUR) FROM PS_FDC_SCHED_TAO B1 WHERE
B1.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND B1.EMPLID = B.EMPLID AND
B1.EMPL_RCD = B.EMPL_RCD AND B1.DUR < A.DUR) AND B.TL_QUANTITY < 56
--------------------------------------------------------------------------------------------
---
AE STEP: 50 ---
SQL Object ID: KREPH14 --- UPDATE PS_TL_TA_RESEQX A SET A.TL_QUANTITY =
A.TL_QUANTITY - (SELECT B.TL_QUANTITY FROM 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 = B.DUR AND B.TRC = 'KPHOL') WHERE A.TRC = 'SUM' AND
EXISTS (SELECT B.TL_QUANTITY FROM 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
= B.DUR AND B.TRC = 'KPHOL')
--------------------------------------------------------------------------------------------
---
AE STEP: 55 ---
SQL Object ID: KREPH15 --- %INSERTSELECT(TL_IPT1, TL_IPT1 A, SEQ_NBR =
A.SEQ_NBR + 1,TRC = B.TRC,TL_QUANTITY = B.TL_QUANTITY,TL_RULE_ID = 'KREPH56')
FROM PS_TL_IPT1 A, PS_TL_TA_RESEQX B WHERE A.PROCESS_INSTANCE =
%BIND(PROCESS_INSTANCE) AND B.TRC = 'KPHOL' AND A.PROCESS_INSTANCE =
B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.DUR
= B.DUR AND A.SEQ_NBR = (SELECT MAX(A1.SEQ_NBR) FROM PS_TL_IPT1 A1 WHERE
A1.PROCESS_INSTANCE = A.PROCESS_INSTANCE AND A1.EMPLID = A.EMPLID AND
A1.EMPL_RCD = A.EMPL_RCD AND A1.DUR = A.DUR)
--------------------------------------------------------------------------------------------
---
AE STEP: 60 ---
SQL Object ID: KREPH18 --- DELETE FROM PS_TL_IPT1 A WHERE
A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND A.TRC = 'GPREX' AND EXISTS
(SELECT 'X' FROM PS_TL_TA_RESEQX B WHERE B.TRC = 'KPHOL' AND A.PROCESS_INSTANCE
= B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND
A.DUR = B.DUR)
--------------------------------------------------------------------------------------------
---
AE STEP: 65 ---
SQL Object ID: KREPH19_1 --- %INSERTSELECT(TL_IPT1, TL_IPT1 A, SEQ_NBR =
A.SEQ_NBR + 10,TRC = 'GPREX',TL_QUANTITY = B.TL_QUANTITY,TL_RULE_ID =
'KREPH56',PUNCH_TYPE=1) FROM PS_TL_IPT1 A, PS_TL_TA_RESEQX B WHERE
A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND A.PROCESS_INSTANCE =
B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND
A.DUR = B.DUR AND A.TRC = 'KPHOL' AND B.TRC = 'SUM'
--------------------------------------------------------------------------------------------
---
AE STEP: 70 ---
SQL Object ID: KREPH20 --- DELETE FROM PS_TL_IPT1 A WHERE
A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND A.TRC = 'AUSPH' AND EXISTS
(SELECT 'X' FROM PS_TL_TA_RESEQX B WHERE B.PROCESS_INSTANCE =
A.PROCESS_INSTANCE AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND
B.DUR = A.DUR AND B.TRC = 'KPHOL' )
