BBG

DECLARE @PHRPA0018 AS TABLE
(
[NUMBER] VARCHAR,
RegID VARCHAR,
[StartDate] VARCHAR,
[EndDate] VARCHAR,
[EmployeeID] VARCHAR,
[AbsenceType] VARCHAR,
[IDOnBehalf] VARCHAR,
[AbsenceAddress] VARCHAR,
[AbsencePhone] VARCHAR,
[Notes] VARCHAR,
[CreateBy] VARCHAR,
[CreateDate] VARCHAR,
[ChangeBy] VARCHAR,
[ChangeDate] VARCHAR,
[TotalAbsenceTaken] VARCHAR
)

SELECT DISTINCT RegID,MAXAS SEQ, FieldID,Value,BizProcessID INTO #TEMP FROM PCMWFRUNBIZDATA WHERE BizProcessID='Leave_AJTM' AND
(FieldID='DelegateTo' OR FieldID='AbsenceType' OR FieldID='StartDate'OR FieldID='EndDate'OR FieldID='TotalAbsenceTaken'OR (FieldID='LeaveConfirmation'AND Value='y'))
AND RegID IN (SELECT RegID FROM PCMWFRUNMAIN WHERE BizflowID='MD_Leave_AJTM' AND SUBSTRING = '2021' )
GROUP BY RegID,FieldID,Value,BizProcessID

DECLARE @ROWSNUMBER INT = (SELECT COUNT FROM PCMWFRUNMAIN WHERE BizflowID='MD_Leave_AJTM' AND SUBSTRING = '2021')
DECLARE @i int = 1

WHILE @i <= @ROWSNUMBER
BEGIN
INSERT INTO @PHRPA0018 VALUES ( @i ,'','','','','','','','','','','','','','')
SET @i = @i + 1
END

UPDATE @PHRPA0018 SET
RegID =A.RegID FROM (SELECT ROW_NUMBER() OVERAS NUMBER,RegID FROM PCMWFRUNMAIN WHERE BizflowID='MD_Leave_AJTM' AND SUBSTRING = '2021' GROUP BY RegID)A
WHERE A.NUMBER = [@PHRPA0018].NUMBER

UPDATE @PHRPA0018 SET
StartDate =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='StartDate')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID

UPDATE @PHRPA0018 SET
EndDate =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='EndDate')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID

UPDATE @PHRPA0018 SET
EmployeeID =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='DelegateTo')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID

UPDATE @PHRPA0018 SET
AbsenceType =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='AbsenceType')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID

UPDATE @PHRPA0018 SET
TotalAbsenceTaken =A.Value FROM (
SELECT DISTINCT RegID,Value,BizProcessID FROM #TEMP WHERE BizProcessID='Leave_AJTM' AND
(FieldID='TotalAbsenceTaken')
GROUP BY RegID,FieldID,Value,BizProcessID
)A
WHERE A.RegID = [@PHRPA0018].RegID

INSERT INTO PHRPA0018
SELECT
[StartDate]
,[EndDate]
,[EmployeeID]
,[AbsenceType]
,[IDOnBehalf]
,[AbsenceAddress]
,[AbsencePhone]
,[Notes]
,[CreateBy]
,[CreateDate]
,[ChangeBy]
,[ChangeDate]
,[TotalAbsenceTaken]
FROM @PHRPA0018
DROP TABLE #TEMP

Mang Wanto,

Maaf ya ada update filter data untuk tarik data dari PCMWFRUNBIZDATA ke PHRPA0018, karena dengan filter yang dipake kemarin ada data yang seharusnya terhitung sebagai cuti, tapi tidak masuk ke PHRPA0018.

Perubahannya adalah, filter leave confirmation = "y" tidak digunakan, digantikan dengan filter data di field "FlowType=400" di table PCMWFRUNMAIN.

Terlampir catatannya yaa.

Db: remote.minovais.com,1438\SQLSERVER2012
Database: MinovaES_BBG_Dev
Contoh RegID : 5594, 55495, 55496
Tabel: PCMWFRUNMAIN, PCMWFRUNBIZDATA, PHRPA0018

Salam,

Ika